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.

Chloride

Why don't you just do an INSERT with ON DUPLICATE KEY UPDATE? – Virginavirginal

@Virginavirginal i tried that but it doesnt work. Documents said it will cause errors in presence of multiple keys – Chloride

in mysql there is an replace. you can may use this – Earleanearleen

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. – Barksdale

@Barksdale : datatypes aint an issue. I have edited to avoid confusion. – Chloride

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). – Barksdale

@Barksdale 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 */ – Chloride

Answer 1

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

Birthstone

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

Answer 2

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

Mra

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

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

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

Answer 3

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.

Matrona

Answer 4

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:

1. there must be END IF for every IF statement;
2. 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.

Crewelwork

Answer 5

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**

Malawi