Update a a column in some specific items of a table, with values from a column in another table-CodePudding

I have 3 tables. I want to update all Image rows from table 1, with local image from table 3.

Table 1 is liked with table 2 by ID. Table 2 and 3 are linked by itemRef.

As an example of what I want: is that ID 1 from table 1, gets image A. Because ID from table 1, is itemRef = 14 in table 2, and itemRef = 14 in table 3 has image A.

╔════╦═══════╗
║ ID ║ Image ║
║ 1  ║       ║
║ 2  ║       ║
║ 3  ║       ║
║ 4  ║       ║
║ 5  ║       ║
║ 6  ║       ║
║ 7  ║       ║
║ 8  ║       ║
╚════╩═══════╝

╔════╦═════════╗
║ ID ║ ItemREF ║
║ 1  ║ 14      ║
║ 2  ║ 15      ║
║ 3  ║ 16      ║
║ 4  ║ 17      ║
║ 5  ║ 18      ║
║ 6  ║ 19      ║
║ 7  ║ 20      ║
║ 8  ║ 21      ║
╚════╩═════════╝



╔═════════╦═════════════╗
║ ItemREF ║ Local Image ║
║ 14      ║ A           ║
║ 15      ║ B           ║
║ 16      ║ C           ║
║ 17      ║ D           ║
║ 18      ║ E           ║
║ 19      ║ F           ║
║ 20      ║ G           ║
║ 21      ║ H           ║
╚═════════╩═════════════╝

This is what I've tried so far:

update table1
set table1.image = table3.local_image
where table1.id in (select table3.local_image 
                    from table1, table2, table3
                    where table1.id = table2.id 
                      and table2.itemREF = table3.itemREF

Can you help me to make this?

CodePudding user response：

Use proper joins in the UPDATE statement like this:

UPDATE table1 t1
INNER JOIN table2 t2 ON t2.id = t1.id
INNER JOIN table3 t3 ON t3.itemREF = t2.itemREF
SET t1.image = t3.local_image;