33

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.

Yaroslav
  • 6,476
  • 10
  • 48
  • 89
AdamLazaruso
  • 562
  • 2
  • 6
  • 13

6 Answers6

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.

Robin Castlin
  • 10,956
  • 1
  • 28
  • 44
  • 1
    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. :) – AdamLazaruso Sep 28 '12 at 12:19
  • Good to point out that `UNIQUE` columns act the same way as `PRIMARY`. I was unsure whether it would cause issues or not. – The Thirsty Ape Jul 17 '13 at 16:57
  • how do you know it was an insert or an update for later on operations? – Alfonso Fernandez-Ocampo Jan 26 '15 at 14:51
  • 1
    http://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. ` – Robin Castlin Feb 05 '15 at 10:54
  • 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) – Dennis Heiden Mar 11 '16 at 15:09
  • It works when two of "tbl_id" and "row_nr" if exists other wise insert is not working – Samir Feb 15 '18 at 20:07
13

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

Holger Just
  • 52,918
  • 14
  • 115
  • 123
DocJones
  • 649
  • 7
  • 26
  • 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. :) – AdamLazaruso Sep 28 '12 at 12:26
  • 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. – Robin Castlin Sep 28 '12 at 13:09
6

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')
Robert
  • 25,425
  • 8
  • 67
  • 81
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
ChatGPT
  • 5,334
  • 12
  • 50
  • 69
  • 2
    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 Mar 13 '13 at 16:57
  • 1
    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. – Sunian314 Mar 13 '13 at 19:01
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

NilsB
  • 1,154
  • 1
  • 16
  • 42
  • Can I use multiple values on this? like with: `INSERT INTO table VALUES (1, 2, 3), (4, 5, 6)` and so on..? – Robert de Jonge Feb 13 '17 at 14:00
  • 2
    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. – PrestonDocks Jun 11 '17 at 10:27
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');
Martin Davies
  • 4,436
  • 3
  • 19
  • 40
Christian T.
  • 1