如果存在其他SQL语句，则进行更新 [英] UPDATE if exists else INSERT in SQL

问题描述

我正在尝试执行一个SQL查询，以如果存在则插入，则进行更新"

I'm trying to implement a SQL query to "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查询:

Below is the SQL query I tried:

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)

我不断收到此错误消息:

I keep getting this error message:

#1064-您的SQL语法有误；请查看与您的MySQL服务器版本相对应的手册，以在'IF EXISTS(SELECT * FROM Allowance WHERE EmployeeID = 10000001 and Year = 2014 an '在第1行"

"#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 "

有人可以帮忙吗?

推荐答案

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

The below query will fulfill your requirement.

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

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