2

I have a users table with autoincrement id as primary key, and unique key on email. The email and name fields are from facebook.

+----+-------+------+
| id | email | name |
+----+-------+------+

And a facebook users table,

+--------+----------+
| userId | fbUserId |
+--------+----------+

The userId in the facebook table will be a foreign key value, referencing id in the users table. The fbUserId is guaranteed to be unique by facebook. The reason I split these tables up is I plan to have more social logins in the future, and I'm trying make my schema future proof. Each additional table would also have a userId, linking it back to users.

Right now my insert update query is this

BEGIN;
    INSERT IGNORE INTO users 
    (id,email,name) 
    VALUES (0,?,?);

    INSERT IGNORE INTO users_facebook
    (userId, fbUserId)
    VALUES (LAST_INSERT_ID(), ?);
COMMIT

From what i understand, ON DUPLICATE KEY UPDATE only applies to a single row in a single table. What i'd like to do is if email or name is different for an existing fbUserId, i'd like to update the changed fields. How would i do this?

Also, any thoughts on my current query? I'm quite new to mysql.

  • Sorry i wasn't clear, id in users is the primary key, and userId is the foreign key. I want each user to have a unique entry in users, but they may exist in multiple social login tables, like facebook, google, twitter, etc. – Eric Guan Jun 1 '17 at 21:55
0

Read the comments in the manual on this page https://dev.mysql.com/doc/refman/5.6/en/insert-on-duplicate.html The technique involves id=LAST_INSERT_ID(id) in the UPDATE clause.

Also near the end of the section on the function: https://dev.mysql.com/doc/refman/5.7/en/information-functions.html#function_last-insert-id

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.