Update MySQL NULL values based on join from another table

Question

I have two tables, one Master and one ExtraData. Both tables share columns FirstName, LastName, Gender and A_Id.

The query I am working on should compare the two tables and UPDATE any NULL values for A_Id in Master using A_Id in Extra.

What is the best way to do this? I could compare CONCAT(FirstName, LastName, Gender) but I'm stuck on how to update the column based on a JOIN.

Chris Trahey · Accepted Answer · 2012-07-10 15:59:26Z

4

You can use many criteria in a join, and then simply set a column in one source table to the value of a column in the other table:

UPDATE Master 
JOIN ExtraData 
  ON Master.FirstName = ExtraData.FirstName
  AND Master.LastName = ExtraData.LastName
  AND Master.Gender = ExtraData.Gender
SET Master.A_Id = ExtraData.A_Id 
WHERE Master.A_Id IS NULL

Note that the JOIN condition can be made more succinct (because the columns are named the same): JOIN ExtraData USING (FirstName, LastName, Gender)

answered Jul 10, 2012 at 15:59

Chris Trahey

18.3k1 gold badge44 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

citizenen Over a year ago

Ahh interesting, I've never understood USING before. That's far simpler than I expected. Thanks!

Chris Trahey Over a year ago

Yeah, USING is the only motivation I have for naming primary keys like this: student_id instead of students.id, since it makes it so easy :-)

Collectives™ on Stack Overflow

Update MySQL NULL values based on join from another table

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related