sql if exists update else insert not working

Question

i have a table with 3 generic keys which are also foreign keys. This is my query --

        IF (EXISTS(SELECT * FROM table1 WHERE col_1 =4))
        BEGIN 
        UPDATE table1 
    SET col_2 = 3,
    col_3 = 100
        WHERE col_1 = 4 
        END
        ELSE 
        BEGIN
        INSERT INTO table1 
    (col_1, col_2, col_3) 
        VALUES(4, 2, 27)
        END

This gives me a syntax error. Engine used InnoDB. Collation: utf8_swedish_ci

I tried this too --

              INSERT INTO table1
    (col1, col2, col3)
     VALUES
    (:val1, :val2, :val3)
    ON DUPLICATE KEY UPDATE
    col2=:val2,
    col3=:val3

This doesn't work properly and only insert the rows inspite of having duplicate keys.

Solution

Generally for scenarios where you want to : "update/delete an existing record if present or insert a new row if the record doesn't exist" , you can use MERGE Command provided by Oracle.

Link : https://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_9016.htm

PSB the below example provided by Oracle:

CREATE TABLE bonuses (employee_id NUMBER, bonus NUMBER DEFAULT 100);

INSERT INTO bonuses(employee_id)
   (SELECT e.employee_id FROM employees e, orders o
   WHERE e.employee_id = o.sales_rep_id
   GROUP BY e.employee_id); 

SELECT * FROM bonuses;

EMPLOYEE_ID      BONUS
----------- ----------
        153        100
        154        100
        155        100
        156        100
        158        100
        159        100
        160        100
        161        100
        163        100

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*0.1)
     WHERE (S.salary <= 8000);

EMPLOYEE_ID      BONUS
----------- ----------
        153        180
        154        175
        155        170
        159        180
        160        175
        161        170
        179        620
        173        610
        165        680
        166        640
        164        720
        172        730
        167        620
        171        740

Comments

[Saharsh Shah]

@user983983 You can use * instead of 1 but for performance do not use *. Both will work same

[Clockwork-Muse]

Why do you think this will solve the problem, if * should work?

[Clockwork-Muse]

For that matter, where are you getting assignment_question? This still appears to throw the same syntax error, anyways.

[Sachin Vishwakarma]

I was about to suggest the same to OP. Merge is also there in SQL