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;