CODEWITHSUNDEEP
sqljoinsql-update
Tony Giaccone
Tony Giaccone

Reputation: 511

SQL Update using value from join table

I tried using this sql to update a new column in a table from a value in table that already exists. 

 update "PROMOTION" t1  
      set "OFFER_CHAIN_ID" = poc."OFFER_CHAIN_ID"
 from "PROMOTION_OFFER_CHAIN" poc 
     inner join "PROMOTION" on "PROMOTION"."ID" = poc."PROMOTION_ID"

What happened is that the first value of the join got replicated in all the subsequent entries. about a both tables. The original table has unique values all the values in the updated column are the same.

Eventually I used this SQL instead. 

update "PROMOTION" t1
    set "OFFER_CHAIN_ID" = poc."OFFER_CHAIN_ID"
from "PROMOTION_OFFER_CHAIN" poc
where 
    t1."ID" = poc."PROMOTION_ID"

This update works and duplicates all the data, 1000 unique elements in the original table, 1000 unique elements in the updated table.

Is this a bug, or is this the expected result?

Upvotes: 0

Views: 160

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270713

SQL is behaving correctly. Your original query is:

 update "PROMOTION" t1 
 --------^ 
      set "OFFER_CHAIN_ID" = poc."OFFER_CHAIN_ID"
      from "PROMOTION_OFFER_CHAIN" poc inner join
           "PROMOTION"
 -----------^
           on "PROMOTION"."ID" = poc."PROMOTION_ID"

Note that the table PROMOTION is mentioned twice. Not good. So, the join takes place, producing lots of rows. Then there is no correlation to the t1 version of the table.

You don't mention the database you are using. In SQL Server, you would just do:

 update p  
      set "OFFER_CHAIN_ID" = poc."OFFER_CHAIN_ID"
      from "PROMOTION_OFFER_CHAIN" poc inner join
           "PROMOTION" p
           on p."ID" = poc."PROMOTION_ID";

Note the alias is used after the update (or table name with if there is no alias). Now the table is mentioned only once, so the update should behave as desired.

Upvotes: 1

Related Questions