Update foreign key field in table to primary key values in another table

Question

After adding SID to the TRANSACTIONS table, I need to update SID column in TRANSACTIONS based off STORE_NBR in the STORES table.

Basically, I want to update the correct SID in transactions to the SID in STORES based on common column STORE_NBR in both tables.

Answer 1

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

Answer 2

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;