CODEWITHSUNDEEP
mysqlsql-update
El Tigre
El Tigre

Reputation: 182

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

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?

Upvotes: 0

Views: 34

Answers (1)

Paul Spiegel
Paul Spiegel

Reputation: 31772

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

Upvotes: 1

Related Questions