update foreign key field in table to primary key values in another table-CodePudding

After adding SID to TRANSACTIONS table, update SID field in TRANSACTIONS based off STORE_NBR using the STORES table. Basically, I want to update the correct SIDs in transactions to the SIDs in STORES based on common column STORE_NBR in both tables.

example that doesn't work:

UPDATE TRANSACTIONS 
SET SID = 
(SELECT STORES.SID 
 FROM STORES 
 INNER JOIN TRANSACTIONS 
 ON STORES.STORE_NBR = TRANSACTIONS.STORE_NBR);

ERROR: more than one row returned by a subquery used as an expression SQL state: 21000

CodePudding user response：

Try this one (pay attention to using aliases for different tables with the same name column in one query):

update transactions
set transactions.sid = stores.sid
from stores
where stores.store_nbr = transactions.store_nbr;

Here's dbfiddle

CodePudding user response：

You can join stores and transactions to set your new column:

UPDATE transactions SET sid = s.sid 
FROM 
transactions t JOIN stores s ON t.store_nbr = s.store_nbr;

If you assume there could occur transactions without corresponding store and you don't want to leave those entries NULL, you can use LEFT JOIN and COALESCE to force a certain value for them, as example 0:

UPDATE transactions SET sid = COALESCE(s.sid,0)
FROM 
transactions t LEFT JOIN stores s ON t.store_nbr = s.store_nbr;