SQL - IF EXISTS UPDATE ELSE INSERT Syntax Error

Question

I have the following SQL query   IF EXISTS SELECT   FROM component psar WHERE tbl id    2  AND row nr    1       UPDATE component psar       SET col 1    1   col 2    1   col 3    1   col 4    1   col 5    1                    col 6    1   unit    1   add info    1   fsar lock    1       WHERE tbl id    2  AND row nr    1   ELSE      INSERT INTO component psar  tbl id  row nr  col 1  col 2  col 3  col 4                                  col 5  col 6  unit  add info  fsar lock      VALUES   2    1    1    1    1    1    1    1    1    1    N     Ignore the fact that I m trying to set every column to  1   It s just example data      Anyways  executing this query returns a syntax 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      component psar WHERE tbl id    2  AND row nr    1   UP  at line 1   I ve been staring at it and searching the internet for a good half an hour and just can t find this supposed syntax error  It s probably going to end up being something really dumb that I m missing but I could use you guys  help on this one

User · Answer

Here is the link to documentation INSERT     ON DUPLICATE Statement

User · Answer

Use the following Statement   IF EXISTS SELECT   FROM prueba   then   UPDATE prueba   SET nombre    1   apellido    1     WHERE cedula    ct  ELSE    INSERT INTO prueba  cedula  nombre  apellido    VALUES   ct    ct    ct

User · Answer

In this approach only one statement is executed when the UPDATE is successful    -- For each row in source BEGIN TRAN      UPDATE target SET  lt target columns gt     lt source values gt  WHERE  lt target expression gt   IF    ROWCOUNT   0     INSERT target   lt target columns gt   VALUES   lt source values gt    COMMIT

User · Answer

You have to add THEN  IF EXISTS SELECT   FROM component psar WHERE tbl id    2  AND row nr    1    THEN UPDATE component psar SET col 1    1   col 2    1   col 3    1   col 4    1   col 5    1   col 6    1   unit    1   add info    1   fsar lock    1  WHERE tbl id    2  AND row nr    1   ELSE  INSERT INTO component psar  tbl id  row nr  col 1  col 2  col 3  col 4  col 5  col 6  unit  add info  fsar lock  VALUES  2    1    1    1    1    1    1    1    1    1    N

User · Answer

INSERT INTO component psar  tbl id  row nr  col 1  col 2  col 3  col 4  col 5  col 6  unit  add info  fsar lock  VALUES  2    1    1    1    1    1    1    1    1    1    N   ON DUPLICATE KEY UPDATE col 1   VALUES col 1   col 2   VALUES col 2   col 3   VALUES col 3   col 4   VALUES col 4   col 5   VALUES col 5   col 6   VALUES col 6   unit   VALUES unit   add info   VALUES add info   fsar lock   VALUES fsar lock    Would work with tbl id and row nr having UNIQUE key   This is the method DocJonas linked to with an example

User · Answer

Isn t this maybe the most elegant   REPLACE  INTO component psar  tbl id  row nr  col 1  col 2  col 3  col 4  col 5  col 6  unit  add info  fsar lock   VALUES  2    1    1    1    1    1    1    1    1    1    N     see  http   dev mysql com doc refman 5 7 en replace html

[mysql] SQL - IF EXISTS UPDATE ELSE INSERT Syntax Error

Examples related to mysql

Examples related to sql