我正在尝试实现一个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/