表如上,我不把ID作为唯一判断是否重复,因为我需要新增的时候会自动生成ID(一个ID生成器给的ID值,就不能拿它作为唯一键判断是否重复),我要拿STUDENT_ID作为唯一判断
IF EXISTS(SELECT * FROM `student` WHERE STUDENT_ID='20200001') THEN
BEGIN
UPDATE `student` SET `ID`='1', `NAME`='王五', `STUDENT_ID`='20200001' WHERE `student_id`='20200001';
END;
ELSE
BEGIN
INSERT INTO `student`(`ID`, `NAME`, `STUDENT_ID`) VALUES('1', '王五', '20200001');
END;
END IF;
以上是我自己写的但是有报错
IF EXISTS(SELECT * FROM `student` WHERE STUDENT_ID='20200001') THEN
BEGIN
UPDATE `student` SET `ID`='1', `NAME`='王五', `STUDENT_ID`='20200001' WHERE `student_id`='20200001'
> 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 `student` WHERE STUDENT_ID='20200001') THEN
BEGIN
UPD' at line 1
> 时间: 0s
请问应该怎么写?
相关问题
- SQL join to get the cartesian product of 2 columns
- sql execution latency when assign to a variable
- Difference between Types.INTEGER and Types.NULL in
- php PDO::FETCH_ASSOC doesnt detect select after ba
- sqlyog export query result as csv
mysql if else 格式是
IF EXISTS(SELECT * FROM
student
WHERE STUDENT_ID='20200001') THENUPDATE
student
SETID
='1',NAME
='王五',STUDENT_ID
='20200001' WHEREstudent_id
='20200001';ELSE
INSERT INTO
student
(ID
,NAME
,STUDENT_ID
) VALUES('1', '王五', '20200001');END IF;
先把STUDENT_ID设置为唯一索引。
INSERT INTO tableName(STUDENT_ID,
NAME
) VALUE("value1","value2") ON DUPLICATE KEY UPDATE STUDENT_ID="value1",NAME
="value2"同学: mysql 扩展语法 replace 了解下。
https://dev.mysql.com/doc/refman/8.0/en/replace.html
语法有问题,中间多了几个分号
我觉得你搞错了,Id肯定是唯一的阿,为什么不能把Id作为判断是否重复的标准?如果这个Id是可重复的,那这个Id是起什么作用的?
参考这个试一下。
https://stackoverflow.com/questions/27627825/select-if-exists-then-else-on-mysql