Tim Rayworth Tim Rayworth - 7 months ago 6
SQL Question

UPDATE field from table 1 to table 2 where the field joining them is not unique in table 2

I'm trying to update a column in one table1 ('keyword') with data (from 'data') in table2 where they joined on 'itemid'. 'Itemid' is not unique in table2, however, and I'm stuck on how to get the data from the record that matches the correct 'field' to update table1, as in the tables below:

table1:

|itemid | keywords
-------------------
| 1 |
| 2 |
| 3 |
-------------------


table2:

|itemid | field | data
---------------------------------------------------------
| 1 | author | Shakespeare
| 1 | title | Hamlet
| 1 | topics | love, loyalty, treason
| 2 | author | Dickens
| 2 | title | Christmas Carol
| 2 | topics | greed, reconciliation
| 3 | author | Melville
| 3 | title | Moby-Dick
| 3 | topics | madness, immortality, sea
---------------------------------------------------------


what I want to achieve is this in table1:

|itemid | keywords
-----------------------------------------
| 1 | love, loyalty, treason
| 2 | greed, reconciliation
| 3 | madness, immortality, sea
-----------------------------------------


What is the right query to accomplish this? I've tried these queries, which don't throw any errors, but they don't update any lines.

UPDATE table1 AS t1, table2 AS t2 SET t1.keywords=t2.data WHERE t1.itemid=t2.itemid AND t2.field='topics'

UPDATE table1 AS t1
JOIN table2 AS t2
ON t1.itemid=t2.itemid
SET t1.keywords=t2.data
WHERE t2.field='topics'

UPDATE table1 AS t1, (SELECT itemid, data FROM table2 WHERE field='topics') AS t2 SET t1.keywords=t2.data


I've looked here and found lots of related UPDATE posts, but none seemed to address this issue. I'm sure it's something simple that I'm missing.

Answer
UPDATE table1 t1, table2 t2
SET t1.keywords = t2.data
WHERE t1.itemid = t2.itemid
  AND t2.field = 'topics'