Updating a field within a table based on a field within the same table

Question

HI ALL

I wish to update a row within my table from a row within that same table,

I have a table called INVENTORY. it stores PRICES for products. I have SALES codes and I have STOCK codes which are related.

I wish to transfer all the PRICING from the SALES codes to the STOCK codes.

I wish to do something like this:

update INVENTORY 
set PRICE = (SELECT PRICE WHERE CODE = "SALES CODE EQUIVALENT OF THE STOCK CODE IM IN")
WHERE 
CODE = "SOME STOCK CODE"

a SALES CODE would look like this "U_12345", its STOCK code equivalent would look like "S_12345"

thanks

Answer 1

UPDATE INVENTORY
SET PRICE = i_sales.PRICE
FROM INVENTORY, INVENTORY i_sales
WHERE INVENTORY.CODE LIKE 'S\_%'
  AND i_sales.CODE = REPLACE(INVENTORY.Code, 'S', 'U')

This updates prices for all 'stock' records in INVENTORY with prices from the corresponding 'sales' records.

If you would prefer to update individual prices, change WHERE to something like this:

WHERE i_stock.CODE = 'S_12345'

or this:

WHERE i_stock.CODE IN ('S_12345', 'S_12346')

Note: The FROM syntax used is based on this doc page.

Answer 2

You're very close, you just need to specify which table you're selecting from as part of your sub-query...

update INVENTORY 
set PRICE = (SELECT PRICE FROM your_table WHERE CODE = "SALES CODE EQUIVALENT OF THE STOCK CODE IM IN")
WHERE 
CODE = "SOME STOCK CODE"

Even if "your_table" is INVENTORY, you still need to specify it in there.

The only time it gets 'tricky' is when your selecting from the same table that you're update, AND when you need a value from the updated record in the SELECT statement. In that case you need to differentiate between the two references, using an alias. For example...

update INVENTORY 
set PRICE = (SELECT PRICE FROM INVENTORY AS [new_price] WHERE [new_price].CODE = INVENTORY.NEW_CODE)
WHERE 
CODE = "SOME STOCK CODE"