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

Question

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.

You might want to look at this question: http://stackoverflow.com/questions/224732/sql-update-from-one-table-to-another-based-on-a-id-match — drdwilcox, Nov 21 '11 at 17:02
Curious: what does `SELECT COUNT(*) FROM table2 WHERE field='topics'` return? — ypercubeᵀᴹ, Nov 21 '11 at 17:13
@ypercube: about 1500, which matches the total number of itemids in table1, in this example. About half of those are empty, but not null. — Tim Rayworth, Nov 21 '11 at 17:24
@TimRayworth: What are the datatypes of `keywords` and `data`? Are they compatible? — ypercubeᵀᴹ, Nov 21 '11 at 17:39
varchar(200) and text. Are those compatible? I think I got it figured out. Thanks to all! — Tim Rayworth, Nov 21 '11 at 20:05

score 1 · Accepted Answer · answered Nov 21 '11 at 17:03

1

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

answered Nov 21 '11 at 17:03

Ryan

26,884
9
56
83

I tried this, but got "0 rows affected". Isn't this the same as query #1 above, but without "AS" to set the alias? Could the issue be with my actual syntax? Here it is with my real names instead of the example text: `UPDATE master_sobi2_item t1, master_sobi2_fields_data t2 SET t1.metakey = t2.data_txt WHERE t1.itemid = t2.itemid AND t2.fieldid = 18` – Tim Rayworth Nov 21 '11 at 17:33
Your syntax looks fine and the query looks correct. Best guess would be to check the data to make sure it lines up and that 't2.fieldid=18' corresponds to your data – Ryan Nov 21 '11 at 18:32
You are right btw, the queries are identical. Note that if you run for example `UPDATE table1 t1 SET t1.keywords = 'blah' WHERE t1.itemid = 2 `, then a single row will be affected. If you run it a second time, then no rows will be affected as no changes are made to the data. Might this be what you are seeing on your actual data? – Ryan Nov 21 '11 at 18:47
This one worked, you were right. I tracked it back and after trying your solution the first time, I missed that it updated properly. Each subsequent attempt with the answers above, kept updating 0 rows. Thanks so much for helping! – Tim Rayworth Nov 21 '11 at 20:01

score 0 · Answer 2 · answered Nov 21 '11 at 17:02

0

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

answered Nov 21 '11 at 17:02

ypercubeᵀᴹ

113,259
19
174
235

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

2 Answers2