SQL query Update with Subquery

Question

I need to update the column stock_ID in tbl_Item_history, and the values should be from stock_ID of tbl_Stock_list. I try to write the query but it does'nt work

UPDATE tbl_item_history
SET Stock_ID = (
SELECT tbl_stock_list.Stock_ID as Stock_ID from tbl_item_history, tbl_stock_list
WHERE tbl_item_history.IH_ITEMNO = tbl_stock_list.Stock_Code)

It comes with Subquery returns more than 1 values.....

Any help would be appreciated. TIA 😊

Answer 1

Try this:

UPDATE ih
SET Stock_ID = sl.Stock_ID
FROM tbl_item_history ih
INNER JOIN tbl_stock_list sl
ON ih.IH_ITEMNO = sl.Stock_Code;