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.

Why don't you just do an `INSERT` with `ON DUPLICATE KEY UPDATE`? — Barmar, Jan 09 '14 at 10:59
@Barmar i tried that but it doesnt work. Documents said it will cause errors in presence of multiple keys — user983983, Jan 09 '14 at 11:03
Why do you use _characters_ for the comparisons, but _numbers_ for the insert? What's the type of the columns? Note, unless you execute this in a transaction (and lock the entire table), you might get some funny results. — Clockwork-Muse, Jan 09 '14 at 12:07
@Clockwork-Muse : datatypes aint an issue. I have edited to avoid confusion. — user983983, Jan 09 '14 at 12:18
Well, what's the syntax error? That should give you some clue. And the `ON DUPLICATE` isn't working because the "key" is all _three_ columns. Probably how I'd deal with this is - attempt the update, and if nothing was affected, insert a new row (which _still_ requires the table be locked, unfortunately). — Clockwork-Muse, Jan 09 '14 at 12:25
@Clockwork-Muse Error is -- SQL Error (1064): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IF (EXISTS(SELECT * FROM table1 WHERE col1 = 4)) BEGIN' at line 1 */ — user983983, Jan 09 '14 at 12:44

score 1 · Answer 1 · answered Jul 19 '17 at 07:29

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

I was about to suggest the same to OP. Merge is also there in SQL — Sachin Vishwakarma, Sep 06 '19 at 12:08

score 0 · Answer 2 · answered Jan 09 '14 at 11:03

0

Try this:

IF EXISTS (SELECT 1 FROM table1 WHERE col_1 = '4') THEN 
BEGIN 
    UPDATE assignment_question 
    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

answered Jan 09 '14 at 11:03

Saharsh Shah

28,687
8
48
83

@user983983 You can use `*` instead of 1 but for performance do not use `*`. Both will work same – Saharsh Shah Jan 09 '14 at 11:06
Why do you think this will solve the problem, if `*` should work? – Clockwork-Muse Jan 09 '14 at 12:11
For that matter, where are you getting `assignment_question`? This still appears to throw the same syntax error, anyways. – Clockwork-Muse Jan 09 '14 at 13:43

score 0 · Answer 3 · answered Apr 25 '14 at 03:18

You can try the below code as an alternative to if exists

SELECT COUNT(*) INTO COUNT FROM TABLE1 WHERE COL_1 = '4'
IF (COUNT > 0) THEN
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

Hope this helps.

score 0 · Answer 4 · answered Jul 25 '17 at 16:38

DELIMITER //
CREATE PROCEDURE p()                                                                                                                                                                     
BEGIN
    IF EXISTS(SELECT * FROM table1 WHERE col_1 = 4) THEN
        UPDATE table1 SET col_2 = 3, col_3 = 100 WHERE col_1 = 4;
    ELSE
        INSERT INTO table1 VALUES(4, 2, 27);
    END IF;
END//
DELIMITER ;

Problems:

there must be END IF for every IF statement;
I created my procedure with mysql so according to documentation:

If you use the mysql client program to define a stored program containing semicolon characters, a problem arises. By default, mysql itself recognizes the semicolon as a statement delimiter, so you must redefine the delimiter temporarily to cause mysql to pass the entire stored program definition to the server.

score 0 · Answer 5 · answered Sep 19 '17 at 12:01

try to put THEN before BEGIN also try to put END IF after the last row

hope this work :

    IF (EXISTS(SELECT * FROM table1 WHERE col_1 =4)) **THEN**
    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
    **END IF**

sql if exists update else insert not working

5 Answers5