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.
x
╔════╦═══════╗║ 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 table1set table1.image = table3.local_imagewhere table1.id in (select table3.local_image from table1, table2, table3 where table1.id = table2.id and table2.itemREF = table3.itemREFCan you help me to make this?
Advertisement
Answer
Use proper joins in the UPDATE statement like this:
UPDATE table1 t1INNER JOIN table2 t2 ON t2.id = t1.idINNER JOIN table3 t3 ON t3.itemREF = t2.itemREFSET t1.image = t3.local_image;