9

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?

Improve this question
4
  • Can't you make composite PK? Commented Mar 29, 2015 at 8:35
  • How would that help me? They are all FK's pointing to other tables (Updated topic). Even tho eventID might be 4 activityID could be 2. It's not guaranteed that an event is having an acitivty assigned. Commented Mar 29, 2015 at 8:48
  • You are doing something wrong. You want check existence by specified 2 fields, but insert different fields. Commented Mar 29, 2015 at 8:57
  • It was just an example, I know it's wrong. Commented Mar 29, 2015 at 9:00

3 Answers 3

Reset to default
8

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

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

But the table eventCounter was made to be used for all events and activities, so the ID's can't be PK's or "unique". And the thing is I have 2 columns that can be changed/inserted both eventID and activityID.
Seems like I'm about to have a solution after messing a bit with your edit - the only problem is the IF syntax imgur.com/uMfV75G any idea?
I don't think an IF statement works like that outside a stored procedure or function in MySQL
3

As 2021, things changed! So i'm updating the old @Jcl's answer:

One thing you have to bear in mind is that this statement only works if a duplication occurs either to PRIMARY KEY or UNIQUE INDEX in case of inserting (so not only PK but index also works)!

SQL command:

INSERT INTO fooTable (RowNum, name, lastname, userID, score) VALUES(3,'Michael','Widenius','1012141618',19.75)
ON DUPLICATE KEY UPDATE name = 'Allan', lastname = 'Larsson', userNum = '2022242628', score = 10.50;

Note:

  • The use of VALUES() to refer to the new row and columns is deprecated (Reference)
  • Command tested on latest MYSQL 8.0 and PHP Mysqli too.
  • Command above is only to show you, how to update multiple rows; in a real world situation, mostly you don't need to update all name, lastname and userID in case that record is already exist and score would be enough!

I found it pretty straight and easy to perform, you can check duplication on almost every important field in your case (using UNIQUE INDEXs)! just remember the very first line...

Improve this answer

Comments

1

Alternatively, if anyone looking for a single query statement without any extra process to create an array to set the values, can try this query method. This is just an improvement over @Shahaboddin's answer.

INSERT INTO eventcounter 
    (
    userID,
    activityID
    )
SELECT userID, activityID FROM eventCounter WHERE userID=1 and eventID=1
ON DUPLICATE KEY UPDATE 
    userID = VALUES(userID),
    activityID = VALUES(activityID);

Note:

  • Tested on MySQL 5.6
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.