Insert or update if already exists

Question

merge into bonuses 
using( select * from bonuses)s ON s.employee_id = '111'   
WHEN MATCHED THEN update set bonus='555' 
WHEN NOT MATCHED THEN insert insert into BONUSES (employee_id) values(115)

Table`s insert queries are

insert into BONUSES (employee_id) values(111)
insert into BONUSES (employee_id) values(112)
insert into BONUSES (employee_id) values(113)
insert into BONUSES (employee_id) values(114)
insert into BONUSES (employee_id) values(115)

If employee_id=111 already exists it should update else it should insert. Kindly help if someone know

So if a BONUS record does NOT exist for employee 111 you will add a bonus record for employee 115 ? Weird. If that's correct then you have an interesting bonus regime in the company.. — BriteSponge, Jun 28 '17 at 13:00
this is just a sample, if this merge query works I`m gonna implement for other work. Pls help me technically do not look for business logic here.. — Mano, Jun 28 '17 at 13:12
It's hard for us to help you if you won't provide requirements we can understand and reason about. I have fixed your syntax errors and showed you how to use MERGE. So what else do you want to know? — APC, Jun 28 '17 at 13:28

score 1 · Answer 1 · answered Jun 28 '17 at 12:09

1

Something like:

MERGE INTO bonuses dst
USING ( SELECT '111' AS employee_id, '555' AS bonus FROM DUAL ) src
ON ( dst.employee_id = src.employee_id )
WHEN MATCHED THEN
  UPDATE SET bonus = src.bonus
WHEN NOT MATCHED THEN
  INSERT ( employee_id, bonus )
  VALUES ( src.employee_id, src.bonus );

answered Jun 28 '17 at 12:09

MT0

143,790
11
59
117

merge query for bonus table that insert data if employee_id not exists else update existing bonus.. – Mano Jun 28 '17 at 12:20

APC · Answer 2 · 2018-12-24T07:06:25.837

Your statement has two syntax errors.

You have repeated the insert keyword.
You have missed the brackets around the on clause conditions. These are mandatory, unlike the join conditions in a normal from clause.

So your code should look like this:

merge into bonuses b
using( select * from bonuses) s 
ON (s.employee_id = 115)  
WHEN MATCHED THEN update set bonus='555' 
WHEN NOT MATCHED THEN insert(employee_id) values(115)
/

However, it doesn't make sense to have the target table in the using clause. It doesn't produce the results you think it's going to...

SQL> select * from bonuses;

EMPLOYEE_ID      BONUS
----------- ----------
        111
        112
        113
        114
        115

5 rows selected.

SQL> merge into bonuses b
  2  using( select * from bonuses) s 
  3  ON (s.employee_id = 115)
  4  WHEN MATCHED THEN update set bonus='555' 
  5  WHEN NOT MATCHED THEN insert (employee_id) values(115)
  6  /  

9 rows merged.

SQL> select * from bonuses;

EMPLOYEE_ID      BONUS
----------- ----------
        111        555
        112        555
        113        555
        114        555
        115        555
        115
        115
        115
        115

9 rows selected.

SQL>

Maybe something like this would suit you?

merge into bonuses b
using( select * from employees) e 
    ON ( b.employee_id = e.employee_id ) 
WHEN MATCHED THEN 
    update set bonus= 555 
WHEN NOT MATCHED THEN 
    insert (employee_id) values (e.id)

If you don't have a source of employee IDs distinct from the BONUSES table you can use the DUAL table to fake it:

SQL> merge into bonuses b
  2  using( select 115 as employee_id, 555 as bonus from dual union all
  3         select 116 as employee_id, 555 as bonus from dual) e 
  4      ON ( b.employee_id = e.employee_id ) 
  5  WHEN MATCHED THEN 
  6      update set bonus= e.bonus 
  7  WHEN NOT MATCHED THEN 
  8      insert (employee_id) values (e.employee_id) 
  9  /

2 rows merged.

SQL> select * from bonuses;

EMPLOYEE_ID      BONUS
----------- ----------
        111
        112
        113
        114
        115        555
        116

6 rows selected.

SQL>

merge query for bonus table that insert data if employee_id not exists else update existing bonus..Any example for this will be helpful, data doesn`t matter — Mano, Jun 28 '17 at 12:23

score 0 · Answer 3 · answered Jun 28 '17 at 12:09

0

I think what you're after is something like:

merge into bonuses tgt
using (select '111' employee_id, '555' bonus from dual) src
  on (tgt.employee_id = src.employee_id)
WHEN MATCHED THEN
  update set tgt.bonus = src.bonus 
WHEN NOT MATCHED THEN
  insert (tgt.employee_id, tgt.bonus)
  values (src.employee_id, src.bonus);

As an aside, why are you inserting strings into columns which usually have a datatype of some form of NUMBER? Do these columns really have string datatypes (e.g. VARCHAR2, CHAR, etc)?

answered Jun 28 '17 at 12:09

Boneist

22,910
1
25
40

merge query for bonus table that insert data if employee_id not exists else update existing bonus..Any example for this will be helpful, data doesn`t matter – Mano Jun 28 '17 at 12:21
That's what I've provided you with. – Boneist Jun 28 '17 at 12:37

Insert or update if already exists

3 Answers3