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.

1
  • 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, 2017 at 21:55

2 Answers 2

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

0

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(), ? ); 

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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