Sample Header Ad - 728x90

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

2 votes
2 answers
3593 views
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.
Asked by Eric Guan (151 rep)
May 31, 2017, 08:41 PM
Last activity: Jul 30, 2025, 08:07 PM