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.
Best Answer
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 theUPDATE
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