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.

No, I'm not executing it as a stored procedure. Should I be? — Objectionne
– Objectionne, Commented Sep 28, 2012 at 11:53
Possible duplicate of Insert into a MySQL table or update if exists — Jim Fell
– Jim Fell, Commented Jun 23, 2016 at 17:57

Robin Castlin · Accepted Answer · 2012-09-28 11:54:36Z

61

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.

answered Sep 28, 2012 at 11:54

Robin Castlin

11k1 gold badge30 silver badges44 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Objectionne Over a year ago

This worked great, thanks. I had to make tbl_id and row_nr a UNIQUE key for it to work but in hindsight they should have been unique anyway so that's just another thing done right. :)

The Thirsty Ape Over a year ago

Good to point out that UNIQUE columns act the same way as PRIMARY. I was unsure whether it would cause issues or not.

Alfonso Fernandez-Ocampo Over a year ago

how do you know it was an insert or an update for later on operations?

Robin Castlin Over a year ago

dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html

With ON DUPLICATE KEY UPDATE, the affected-rows value per row is 1 if the row is inserted as a new row, and 2 if an existing row is updated.

Dennis Heiden Over a year ago

I had trouble with inserting and updating a large table (150-200k rows), insert with update on duplicate was a lot faster. (5secs / 1000 rows vs 60secs / 1000rows)

|

Holger Just · Accepted Answer · 2015-01-22 16:07:46Z

13

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

edited Jan 22, 2015 at 16:07

Holger Just

56.5k15 gold badges124 silver badges137 bronze badges

answered Sep 28, 2012 at 11:49

DocJones

6771 gold badge8 silver badges26 bronze badges

2 Comments

Objectionne Over a year ago

I appreciate the sentiment (and your answer to begin with), but showing me an example of how to set a fire like Robin Castlin did is more useful than telling me to search for 'how to make a fire' on Google. :)

Robin Castlin Over a year ago

Although that's true, I for one learn best by examples. Hopefully this is enough for him to know how to use the function in the future.

Robert · Accepted Answer · 2012-09-28 11:54:58Z

5

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

answered Sep 28, 2012 at 11:54

Robert

25.8k8 gold badges70 silver badges86 bronze badges

1 Comment

Jerry Over a year ago

Is there an alternate for mysql?

maxhodges · Accepted Answer · 2013-03-15 11:02:26Z

3

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

edited Mar 15, 2013 at 11:02

answered Oct 15, 2012 at 5:26

maxhodges

5,67113 gold badges54 silver badges72 bronze badges

2 Comments

Sunian314 Over a year ago

I'm going to use this rather than the accepted answer since in my application, I anticipate UPDATE will succeed the vast majority of the time.

Sunian314 Over a year ago

actually, why not use merge statement? merge allows merging a source table into a target table and doing update, insert, and delete where necessary all in one pass.

NilsB · Accepted Answer · 2016-07-18 21:05:43Z

1

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

answered Jul 18, 2016 at 21:05

NilsB

1,1901 gold badge19 silver badges43 bronze badges

2 Comments

Robert de Jonge Over a year ago

Can I use multiple values on this? like with: INSERT INTO table VALUES (1, 2, 3), (4, 5, 6) and so on..?

PrestonDocks Over a year ago

It depends if you want to preserve the primary key/auto increment field. REPLACE will delete the original row if it currently exists before inserting. Also note that if you have a large number of records with indexes, then the deletes can take a long time and in this case are unnecessary if the record already exists.

Martin Davies · Accepted Answer · 2014-01-13 23:46:13Z

0

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

edited Jan 13, 2014 at 23:46

Martin Davies

4,4563 gold badges21 silver badges43 bronze badges

answered Jan 13, 2014 at 23:14

Christian T.

1

Collectives™ on Stack Overflow

SQL - IF EXISTS UPDATE ELSE INSERT Syntax Error

6 Answers 6

6 Comments

2 Comments

1 Comment

2 Comments

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

6 Comments

2 Comments

1 Comment

2 Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related