mysql - 如果存在则更新，否则在 SQL 中插入

Question

我正在尝试执行 SQL 查询以“UPDATE if exists else INSERT”

我的表格(Allowance)如下:

EmployeeID int(8) PK
Year year(4) PK
Month int(2) PK
OverTime decimal(10,2)
Medical decimal(10,2)
Lunch decimal(10,2)
Bonus decimal(10,2)
Allowance decimal(10,2)

下面是我试过的 SQL 查询:

IF EXISTS (SELECT * FROM Allowance WHERE EmployeeID =10000001 and Year = 2014 and Month = 4)
    UPDATE Allowance
    SET OverTime = 10.00, Medical = 10.00, Lunch = 10.45, Bonus =10.10, Allowance = 40.55
    WHERE EmployeeID =10000001 and Year = 2014 and Month = 4
ELSE
    INSERT into Allowance values (10000001,2014,4,10.00,10.00,10.45,10.10,40.55)

我不断收到此错误消息:

"#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 Allowance WHERE EmployeeID =10000001 and Year = 2014 an' at line 1 "

有人可以帮忙吗？？

Answer 1

下面的查询将满足您的要求。

INSERT INTO `ALLOWANCE` (`EmployeeID`, `Year`, `Month`, `OverTime`,`Medical`,
`Lunch`, `Bonus`, `Allowance`) values (10000001, 2014, 4, 10.00, 10.00,
10.45, 10.10, 40.55) ON DUPLICATE KEY UPDATE `EmployeeID` = 10000001