我正在尝试实现一个SQL查询,以“如果存在,则另存为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)

我不断收到此错误消息:



有人可以帮忙吗?

最佳答案

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

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

关于mysql - 如果存在其他SQL语句,则进行更新,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/30569666/

10-12 15:24