MySQL how do I update multiple columns from the same row in another table

Question

I need to update one table that looks like the following.

ItemID  FID1    FID2    FID3    FName1  FName2  FName3  
100084  2545    2536    2535

Using data from another table. The other table looks like:

FID    FName
2545   Name 1
2534   Name 3
2536   Name 2

I want the updated table to look like:

ItemID  FID1    FID2    FID3    FName1  FName2  FName3  
100084  2545    2536    2535    Name 1  Name 2  Name 3

What is a clean way to do this in a single query?

You delete the first table. You alter the second table and add ItemID column. Then you pour gasoline and acid on the file that contained the first table so you never make such an atrocity again. — N.B.
– N.B., Commented Jul 23, 2018 at 20:30

Paul Spiegel · Accepted Answer · 2018-07-23 20:30:11Z

1

I won't ask why you want to store your data in a non-normalized form ;-) - But this should work:

update one_table t
left join other_table t1 on t1.FID = t.FID1
left join other_table t2 on t2.FID = t.FID2
left join other_table t3 on t3.FID = t.FID3
set t.FName1 = t1.Fname,
    t.FName2 = t2.Fname,
    t.FName3 = t3.Fname

answered Jul 23, 2018 at 20:30

Paul Spiegel

31.9k5 gold badges48 silver badges56 bronze badges

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

2 Comments

El Tigre Over a year ago

Thanks! Unfortunately I'm inheriting these tables so that's the excuse I'm going with.

El Tigre Over a year ago

After seeing the horrendous performance of this query and the feedback from you and @n-b I was able to get additional time so I could change the tables. Woot!

Collectives™ on Stack Overflow

MySQL how do I update multiple columns from the same row in 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