In the database there is a table (my_table) with two columns ID and NAME. I want to make a procedure for INSERT / UPDATE. If a match is found - change the value, if not - add.

DELIMITER // CREATE PROCEDURE ins_upd (in x int(10), in y varchar(20)) begin if (x IN (SELECT ID FROM my_table) then UPDATE my_table SET ID=x, NAME=y; else INSERT INTO my_table values (x, y); end if; end//

swears at the syntax, tell me what's wrong?

  • A kind person suggested such a thing as "ON DUBLICATE KEY". But I still swear at the syntax in this line: INSERT INTO my_table values ​​(x, y) ON DUBLICATE KEY UPDATE ID = x, NAME = y; - Hev

1 answer 1

Instead of if else they suggested a handy thing (thanks to Akina) - ON DUPLICATE KEY. With the help of it (and heaps of attempts) such stored procedure turned out:

 DELIMITER // CREATE PROCEDURE ins_upd (new_ID int(10), new_NAME varchar(20)) INSERT INTO my_table (ID, NAME) VALUES (new_ID, new_NAME) ON DUPLICATE KEY UPDATE NAME=new_NAME; //

Long did not work because I wrote DUBLICATE instead of DUPLICATE