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.

Answer 1

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

Answer 2

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

Answer 3

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.

Answer 4

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

Answer 5

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.