MySQL如果行存在更新否则插入 [英] MySQL if row exists update else insert

问题描述

我正在尝试创建一个站点，人们可以在该站点上注册事件+分配给该事件的活动.他们可以选择要参加的人，甚至选择两者.

为此，我制作了一个名为"eventCounter"的表 表格图片
userID，eventID和activityID都是FK在其他表上的内容.

他们应该能够更新其当前的状态"，以便他们在注册活动后可以加入活动.

所以我的问题是:我该如何制作 If else 说行是否存在更新else else

 IF EXISTS(select userID, eventID, activityID from eventCounter where userID=1 and eventID=1)
THEN 
UPDATE eventcounter SET activityID=1 WHERE userID=1; 
ELSE 
INSERT INTO eventcounter (userID, activityID) VALUES(1,1) 

我认为ON DUPLICATE键不起作用，因为我有2列需要检查吗?

解决方案

这就是所谓的Upsert. MySQL语法很奇怪，但是您可以这样做，就像这样:

INSERT INTO eventcounter (userID, eventID, activityID) VALUES(1,1,1)
ON DUPLICATE KEY UPDATE
  activityID = VALUES(activityID)

尽管它仅适用于重复的PK(不适用于您喜欢的任何字段)，因此在这种情况下，仅当userID和eventID组成PK并且您只想更改activityID 时，它才适用>

否则，您可以使用IF-ELSE路线，例如:

DECLARE mycount INT;
SET mycount = (SELECT COUNT(*) FROM eventcounter WHERE userID=1 AND eventID=1);
IF mycount > 0 THEN
  UPDATE eventcounter SET activityID=1 WHERE userID=1 AND eventID=1;
ELSE
  INSERT INTO eventcounter (userID, eventID, activityID) VALUES(1,1,1);
END IF;

免责声明:完全未经测试的代码

I'm trying to make a site where people can sign up for events + an activity assigned to that event. They can choose which one they want to attend, or even both.

For that I made a table named "eventCounter" Image of table
userID, eventID and activityID are all FK poiting to other tables.

They should be able to update their current "status", so they can join the activity after they signed up for the event.

So my question is: How can I make a If else saying if row exists update else insert

IF EXISTS(select userID, eventID, activityID from eventCounter where userID=1 and eventID=1)
THEN 
UPDATE eventcounter SET activityID=1 WHERE userID=1; 
ELSE 
INSERT INTO eventcounter (userID, activityID) VALUES(1,1)

I don't think the ON DUPLICATE key will work as I have 2 columns that needs to be checked?

解决方案

That's what is called an Upsert. The MySQL syntax is pretty weird, but you can do it, something like:

INSERT INTO eventcounter (userID, eventID, activityID) VALUES(1,1,1)
ON DUPLICATE KEY UPDATE
  activityID = VALUES(activityID)

It only works for duplicate PKs though (not for any field you like), so in this case it would only work if userID and eventID compose the PK and you only want to change the activityID

Otherwise, you could go the IF-ELSE route, something like:

DECLARE mycount INT;
SET mycount = (SELECT COUNT(*) FROM eventcounter WHERE userID=1 AND eventID=1);
IF mycount > 0 THEN
  UPDATE eventcounter SET activityID=1 WHERE userID=1 AND eventID=1;
ELSE
  INSERT INTO eventcounter (userID, eventID, activityID) VALUES(1,1,1);
END IF;

Disclaimer: totally untested code

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