CODEWITHSUNDEEP
mysqlsql
AdamLazaruso
AdamLazaruso

Reputation: 572

SQL - IF EXISTS UPDATE ELSE INSERT Syntax Error

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.

Upvotes: 33

Views: 140618

Answers (6)

NilsB
NilsB

Reputation: 1188

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

Upvotes: 1

DocJones
DocJones

Reputation: 677

Here is the link to documentation INSERT ... ON DUPLICATE Statement.

Upvotes: 13

Christian T.
Christian T.

Reputation: 1

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');

Upvotes: 0

ChatGPT
ChatGPT

Reputation: 5617

In this approach only one statement is executed when the UPDATE is successful. 

-- For each row in source
BEGIN TRAN    

UPDATE target
SET <target_columns> = <source_values>
WHERE <target_expression>

IF (@@ROWCOUNT = 0)
   INSERT target (<target_columns>)
VALUES (<source_values>)

COMMIT

Upvotes: 3

Robert
Robert

Reputation: 25753

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')

Upvotes: 5

Robin Castlin
Robin Castlin

Reputation: 10996

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.

Upvotes: 61

Related Questions