How to update primary key table based on foreign key with ON DUPLICATE KEY UPDATE

Question

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.

• Eric Guan: 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.

Answer 1

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

Answer 2

Why do you feel the need for all this "ignore" stuff?
Can you even have more than one User row if you always insert rows with id=0 but ignore any and all duplicates?

How about this instead?

INSERT INTO users ( email, name ) 
VALUES ( ?, ? ); 
INSERT INTO users_facebook ( userId, fbUserId )
VALUES ( LAST_INSERT_ID(), ? );