MySQL SQL - IF EXISTS UPDATE ELSE INSERT 语法错误
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12639407/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL - IF EXISTS UPDATE ELSE INSERT Syntax Error
提问by AdamLazaruso
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.
我一直盯着它并在互联网上搜索了半个小时，但找不到这个假定的语法错误。它可能最终会成为我遗漏的非常愚蠢的东西，但我可以在这个问题上使用你们的帮助。
回答by Robin Castlin
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_idand row_nrhaving UNIQUEkey.
将与工作tbl_id和row_nr有UNIQUE关键。
This is the method DocJonas linked to with an example.
这是 DocJonas 与示例链接的方法。
回答by DocJones
Here is the link to documentation INSERT ... ON DUPLICATEStatement.
这是文档INSERT ... ON DUPLICATEStatement的链接。
回答by Robert
You have to add THEN
你必须添加 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')
回答by Max Hodges
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
回答by NilsB
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')
回答by Christian T.
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');
