Insert first and then update the table using SQL statement

Question

I am trying to use insert and update sql statements.

My table is as follows:

|c1|c2|c3|c4|c5 
|1   2  a  b  c 
|1   3  e  f  g

c3,c4,c5 can have different values. The row can be unique with the combination of C1 and C2 column. I need to be able to check if first row doesn't exists with values c1,c2 then insert the data. If c1,c2 already have the values for eg (1,2) and if the data comes back with the same values for c1,c2 then update c3,c4,c5 with the latest values.

I tried using the following query

INSERT INTO t1 (c1,c2,c3,c4,c5)
VALUES ('1','2','a','b','c') 
ON DUPLICATE KEY 
UPDATE c3='e',c4 = 'f',c5='g';

I am getting a ORA error as follows SQL Command not ended properly (ORA-00933)

Update after response from sagi

MERGE INTO table1 t USING(select '000004' as SENDER,'Receiver' as RECEIVER ,'1030' as IDENTIFIER,'2016' as CREATIONDATEANDTIME,'2' as ACKCODE,'Test' as ACKDESCRIPTION from table1 ) s ON(t.SENDER = s.SENDER and t.IDENTIFIER = s.IDENTIFIER) WHEN MATCHED THEN UPDATE SET t.CREATIONDATEANDTIME = '1213',t.RECEIVER = 'hello' WHEN NOT MATCHED THEN INSERT (t.SENDER,t.RECEIVER,t.IDENTIFIER,t.CREATIONDATEANDTIME,t.ACKCODE,t.ACKDESCRIPTION) VALUES (s.SENDER,s.RECEIVER,s.IDENTIFIER,s.CREATIONDATEANDTIME,s.ACKCODE,s.ACKDESCRIPTION)

Output of query: scenario 1: When there is no data matching the condition(t.SENDER = s.SENDER and t.IDENTIFIER = s.IDENTIFIER), I get an error as follows ORA-30926: Unable to get stable set of rows in the source tables.
Cause: A stable set of rows could not be got because of large dml activity or a non-deterministic activity where clause.
Action: Remove any non-deterministic where clause and reissue dml

Scenario 2: When there is data matching the condition (t.SENDER = s.SENDER and t.IDENTIFIER = s.IDENTIFIER) then in the table, I can see 5 new entries.

Can you please help.

oracle may own mysql, but that doesn't mean that the oracle RDBMS will suddenly start support mysql-specific sql extensions... — Marc B, Feb 10 '16 at 14:12
This is marked as Mysql but you have an Oracle error. What database are you actually using? — HLGEM, Feb 10 '16 at 14:13
Perhaps you need to use [`MERGE`](https://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_9016.htm) statement instead of on duplicate key. — xQbert, Feb 10 '16 at 14:15
@user3384231 follow the link on the merge. It ties directly to oracle documentation with a few examples. Specifically from that link ...`MERGE INTO bonuses D USING (SELECT employee_id, salary, department_id FROM employees WHERE department_id = 80) S ON (D.employee_id = S.employee_id) WHEN MATCHED THEN UPDATE SET D.bonus = D.bonus + S.salary*.01 DELETE WHERE (S.salary > 8000) WHEN NOT MATCHED THEN INSERT (D.employee_id, D.bonus) VALUES (S.employee_id, S.salary*.01) WHERE (S.salary <= 8000);`... — xQbert, Feb 10 '16 at 14:17
The "merge" word in xQbert comment is a link to documentation. — zozo, Feb 10 '16 at 14:17

sagi · Answer 1 · 2016-02-10T15:01:04.323

1

You can use MERGE STATEMENT like this:

MERGE INTO t1 t
USING(select '1' as c1,'2' c2 ,'a' as c3,'b' as c4,'c' as c5 from dual) s
ON(t.c1 = s.c1 and t.c2 = s.c2)
WHEN MATCHED THEN UPDATE SET t.c3 = '1213',t.c4 = 'test'
WHEN NOT MATCHED THEN INSERT  (t.c1,t.c2,t.c3,t.c4,t.c5)
VALUES (S.c1,s.c2,s.c3,s.c4,s.c5)

This basically perform an UPSERT, update else insert. It checks if the values exist, if so - update/deletes them(adjust to code to do what you want) and if not, insert them.

edited Feb 10 '16 at 15:01

answered Feb 10 '16 at 14:22

sagi

40,026
6
59
84

MERGE INTO t1 t USING(select '1' as c1,'2' as c2 ,'a' as c3,'b' as c4,'c' as c5) s ON(t.c1= s.c1 and t.c2 = s.c2) WHEN MATCHED THEN UPDATE SET t.c3 = '1213' AND t.c4 = 'test' WHEN NOT MATCHED THEN INSERT (t.c1,t.c2,t.c3,t.c4,t.c5) VALUES (s.c1,s.c2,s.c3,s.c4,s.c5) -----------> I am getting an ORA error: FROM keyword not found where expected. ORA-00923 – user3384231 Feb 10 '16 at 14:43
@user3384231 My bad, edited the answer. Missed FROM DUAL inside the USING() part. :) will work now. – sagi Feb 10 '16 at 14:47
can I not use same t1 table? according to requirement..I am not supposed to use dual table – user3384231 Feb 10 '16 at 14:49
I tried using t1 table instead of dual but its complaining SQL command not ended properly. – user3384231 Feb 10 '16 at 14:51
Don't use t1 table, its ok to use dual, its a constructed table inside oracle.. Its what you need in this case, anyways, this is the answer – sagi Feb 10 '16 at 14:52
I changed my query to dual....still getting an error for: WHEN MATCHED THEN UPDATE SET t.c3 = '1213' AND t.c4 = 'test' . is there anything wrong? Apologies..i am very new to sql...and also this query will be run from an application that is the reason I was concerned regarding usage of dual table. is there any way to use merge without the use of dual table? – user3384231 Feb 10 '16 at 14:59
Yes, AND is not correct syntax, change it to comma - ',' , I've updated my answer you can just copy it.. @user3384231 And yes, you can use without dual but its not necessary , IF it will cause problems, change the using() to - USING(select distinct '1' as c1,'2' c2 ,'a' as c3,'b' as c4,'c' as c5 from t1) – sagi Feb 10 '16 at 15:00
Thanks. Last question...can you please tell me can I use merge without dual table? – user3384231 Feb 10 '16 at 15:09
Thank you. really appreciate your help. – user3384231 Feb 10 '16 at 15:47
@user3384231 No problem. – sagi Feb 10 '16 at 15:50
I am testing my query and it seems it doesn't work correctly. I edited my question again....will explain further over their. – user3384231 Feb 10 '16 at 16:23
I think you should already open a new question, its messy here @user3384231 – sagi Feb 10 '16 at 16:26
opened a new question http://stackoverflow.com/questions/35320844/issue-with-merge-mysql-stmt – user3384231 Feb 10 '16 at 16:36

score 0 · Answer 2 · answered Feb 10 '16 at 17:50

MERGE INTO table1 t USING(select distinct '000004' as SENDER,'Receiver' as RECEIVER ,'1030' as IDENTIFIER,'2016' as CREATIONDATEANDTIME,'2' as ACKCODE,'Test' as ACKDESCRIPTION from table1 ) s ON(t.SENDER = s.SENDER and t.IDENTIFIER = s.IDENTIFIER) WHEN MATCHED THEN UPDATE SET t.CREATIONDATEANDTIME = '1213',t.RECEIVER = 'hello' WHEN NOT MATCHED THEN INSERT (t.SENDER,t.RECEIVER,t.IDENTIFIER,t.CREATIONDATEANDTIME,t.ACKCODE,t.ACKDESCRIPTION) VALUES (s.SENDER,s.RECEIVER,s.IDENTIFIER,s.CREATIONDATEANDTIME,s.ACKCODE,s.ACKDESCRIPTION)

Added distinct clause in my query and it is working fine. Thanks all for replying to my post and guiding me.

Insert first and then update the table using SQL statement

2 Answers2