MySQL CASE For Registration

Question

I register user via Facebook Login Authentication and works well.

I store facebook_id, facebook_email and facebook_birthday. Here is the query that I would like to run;

Case 1: If this user has facebook_id which exist in MySQL, update facebook_email and facebook_birthday (in case of they change)

Case 2: If this user's facebook_id is not exist in MySQL, insert into facebook_id, facebook_email and facebook_birthday

I tried this but didn't happen. Thanks in advance.

SELECT facebook_id
CASE (SELECT COUNT(*) FROM user_facebook WHERE facebook_id = 123123123)
    WHEN 0 THEN INSERT INTO user_facebook (facebook_id) VALUES (1233)
    WHEN 1 THEN UPDATE user_facebook SET facebook_email = "123@gmail.com"
    ELSE
END
FROM user_facebook

SELECT's, and queries in general, cannot be used like procedural code. (i.e. a SELECT cannot execute an UPDATE or INSERT). — Uueerdo, May 04 '18 at 16:50
Upsert might help in your case. Check this out- https://stackoverflow.com/questions/6107752/how-to-perform-an-upsert-so-that-i-can-use-both-new-and-old-values-in-update-par — manishk, May 04 '18 at 16:52
Sounds like you are looking for an INSERT statement with the `ON DUPLICATE KEY UPDATE` option. — Dave, May 04 '18 at 16:52

score 0 · Accepted Answer · answered May 04 '18 at 16:53

0

The operation you are asking about is called a "merge" or "upsert".

Assuming facebook_id is the primary key, or at least has a unique index, you can add the optional ON DUPLICATE KEY UPDATE clause to an INSERT statement.

INSERT INTO user_facebook
    (facebook_id, facebook_email, facebook_birthday)
    VALUES (123123123, '123@gmail.com', '1998-05-02')
ON DUPLICATE KEY UPDATE
    facebook_email = VALUES(facebook_email)
    facebook_birthday = VALUES(facebook_birthday)

If the facebook_id already exists in the table, the email and birthday will be updated for that record. If not, a new record will be inserted.

answered May 04 '18 at 16:53

Don't Panic

41,125
10
61
80

`INSERT INTO user_facebook (facebook_id, facebook_email, facebook_name, facebook_birthday, facebook_username) VALUES('123123123','mymail@gmail.com','John John','05/11/1998','johnjohn') ON DUPLICATE KEY UPDATE facebook_email = VALUES(facebook_email), facebook_name = VALUES(facebook_name), facebook_birthday = VALUES(facebook_birthday), facebook_username = VALUES(facebook_username)` Thanks for answer. I did it like this but it insert the user even though facebook_id is exist in SQL. Where I'm wrong at? – keremoflu4 May 04 '18 at 19:52
It sounds like you need to add a unique index on `facebook_id`. In order for the `ON DUPLICATE KEY UPDATE` part to be activated, there has to be a duplicate key. If there isn't, it will just insert as usual, so there has to be something there to indicate that it's a duplicate. That can either be a unique index or a primary key (which is unique by definition). – Don't Panic May 04 '18 at 20:02
Before to try that SQL Query, I had a user with '123123123' ID. Except for facebook_id, I have id value which is AUTO_INCREMENT. Is might be something wrong with that? I must use that in query? – keremoflu4 May 04 '18 at 20:07
No, that auto_increment id value is fine, but it isn't going to be useful in this scenario. I'm not saying you should remove it; it could still be useful in general, but the thing that needs to be unique for what you're trying to do here is the facebook_id. – Don't Panic May 04 '18 at 20:11
It worked, thank you very much! I confused about primary and unique. After I made facebook_id unique, it worked. I missed. Thanks again it saved me. – keremoflu4 May 04 '18 at 20:53

MySQL CASE For Registration

1 Answers1