SQL-如果存在更新其他插入语法错误 [英] SQL - IF EXISTS UPDATE ELSE INSERT Syntax Error

问题描述

我有以下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')

忽略我试图将每一列设置为"1"的事实.这只是示例数据. :)

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.

推荐答案

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)

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

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

This is the method DocJonas linked to with an example.

这篇关于SQL-如果存在更新其他插入语法错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！