SQL - IF EXISTS UPDATE ELSE INSERT语法错误

I have the following SQL query:

我有以下SQL查询：

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. :)

忽略我正在尝试将每列设置为“1”的事实。这只是示例数据。 :)

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.

我一直盯着它，在互联网上搜索了半个小时，但却找不到这个假设的语法错误。这可能最终会让我感到非常愚蠢，但我可以在这个问题上帮助你们。

6 个解决方案

#1

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.

可以使用具有UNIQUE键的tbl_id和row_nr。

This is the method DocJonas linked to with an example.

这是DocJonas与示例链接的方法。

#2

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

这是文档INSERT ... ON DUPLICATE语句的链接。

#3

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

#4

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

在此方法中，UPDATE成功时只执行一个语句。

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

#5

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

请参阅：http：//dev.mysql.com/doc/refman/5.7/en/replace.html

#6

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

#1

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)