3

Is it possible to use the merge statement for the single table? I want to check before inserting that if the same set of values are already exists then that record should be updated and if not then it should be inserted. I have searched on the internet but I got the example of merge statement with 2 table. I was trying on a single table but I am not able to get the positive results. Values that I am trying to check will be dynamic. I want to use this query in my application.

I am trying to check(In below case only "Major" column) if two columns are matched then that record should be updated and if not then it should be inserted.

I have created a sample table student. below is the query.

create table student (sid int,name varchar(20),major varchar(10),gpa float,tutorid int, PRIMARY KEY (sid))

insert into student values(101,'Bill','CIS',3.45,102)

insert into student values(102,'Mary','CIS',3.1,null)

insert into student values(103,'Sue','Marketing',2.95,102)

insert into student values(104,'Tom','Finanace',3.5,106)

insert into student values(105,'Alex','CIS',2.75,106)

insert into student values(106,'Sam','Marketing',3.25,103)

insert into student values(107,'Joan','Finance',2.9,102)

Below is the query I am using

merge into student a 
using (select name,major from student) b 
on (a.major=b.major)
when matched then
update set a.name='Rahul'
when not matched then
insert(a.SID,a.major) values(123, 'Temp')

I am getting the below error

ORA-30926: unable to get a stable set of rows in the source tables

Maybe I am doing things completly wrong. Can anyone please help me.

D-Shih
  • 44,943
  • 6
  • 31
  • 51
Rahul Gulwani
  • 198
  • 4
  • 18
  • You are altering the contents of the table you are, at the same time, selecting from. Unfortunately, there is no `IF EXISTS` in PL/SQL so you may attempt using `SELECT COUNT(*) FROM..... WHERE...` and insert only if the result of the select = 0. – FDavidov May 09 '18 at 11:34
  • Thanks, @FDavidov. That is my last option. I can do that. But for that in my application, I have to create 2 separate connection with Database. One for the select statement and other for either update or insert. Which I Don't want. I want to achieve this by a single connection. Hope you got my point – Rahul Gulwani May 09 '18 at 11:38
  • 1
    What you are looking for is (to me) like standing on your hands and wishing to tight the knots of your shoes. Good luck!!!! – FDavidov May 09 '18 at 11:41
  • @FDavidov, so funny, Is like to try to fly stretching the knots of your shoes. – dani herrera May 09 '18 at 11:52
  • Why not use a stored procedure to do it? – Arijit Kanrar May 09 '18 at 12:10
  • Related question: [JPA - create-if-not-exists entity?](https://stackoverflow.com/questions/3562105/jpa-create-if-not-exists-entity/76481208#76481208) – sleske Jun 15 '23 at 09:44

1 Answers1

6

Use SELECT ... FROM DUAL in the USING clause to generate a single row with your data:

SQL Fiddle

Oracle 11g R2 Schema Setup:

create table student (
  sid int,
  name varchar(20),
  major varchar(10),
  gpa float,
  tutorid int,
  PRIMARY KEY (sid)
);

insert into student values(101,'Bill','CIS',3.45,102);
insert into student values(102,'Mary','CIS',3.1,null);
insert into student values(103,'Sue','Marketing',2.95,102);
insert into student values(104,'Tom','Finanace',3.5,106);
insert into student values(105,'Alex','CIS',2.75,106);
insert into student values(106,'Sam','Marketing',3.25,103);
insert into student values(107,'Joan','Finance',2.9,102);

Query 1:

merge into student dst
using (
  SELECT 123 AS sid,
         'Rahul' AS name,
         'Temp' AS major
  FROM   DUAL
) src
on (src.major=dst.major)
when matched then
  update set name=src.name
when not matched then
  insert(SID,name,major) values ( src.sid, src.name, src.major )

Query 2:

SELECT * FROM student

Results:

| SID |  NAME |     MAJOR |    GPA | TUTORID |
|-----|-------|-----------|--------|---------|
| 101 |  Bill |       CIS |   3.45 |     102 |
| 102 |  Mary |       CIS |    3.1 |  (null) |
| 103 |   Sue | Marketing |   2.95 |     102 |
| 104 |   Tom |  Finanace |    3.5 |     106 |
| 105 |  Alex |       CIS |   2.75 |     106 |
| 106 |   Sam | Marketing |   3.25 |     103 |
| 107 |  Joan |   Finance |    2.9 |     102 |
| 123 | Rahul |      Temp | (null) |  (null) |
MT0
  • 143,790
  • 11
  • 59
  • 117