Update a a column in some specific items of a table, with values from a collumn 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 itemRef14 in table 3 have 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 ive 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=tble2.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;

CodePudding user response：

Try this syntax

update table1
set table1.image=table3.local_image
from table1
join table2 on table1.id = table2.id
join table3 on table2.itemREF = table 3.itemREF

NB it is important that this returns one and only one value. If there are more than one you will get an error. If there are no matches the value will be updated to null.
We could add

WHERE table3.itemREF IS NOT NULL;

to avoid overwriting old data with null when there is no match.