2

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

seb
  • 23
  • 3

2 Answers2

1

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"
MatBailie
  • 83,401
  • 18
  • 103
  • 137
  • ok you are right, but still, how would i go about getting this update to parse my entire table, i will have to somehow use a LIKE statement in order to do the whole table? – seb Mar 01 '11 at 01:13
  • I'm not sure what you means, so I guessed and expanded the answer. If it's not what you mean, could you give an example? – MatBailie Mar 01 '11 at 01:14
0
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.

Andriy M
  • 76,112
  • 17
  • 94
  • 154
  • 1
    Several RDBMS's won't allow the syntax used by MS SQL Server, but, as far as i know, most/all allow the subquery version used by the OP. – MatBailie Mar 01 '11 at 11:11
  • @Dems: Until your note I didn't pay attention to the `pervasive` tag and thought the OP just didn't choose to specify which DBMS should be targeted by the answers. But now I've discovered there's a product named *Pervasive PSQL*. It is what is meant by the tag, right? Anyway, your note is relevant irrespectively, thanks. Still, I'm not sure how to change this into using a correlated subquery. There must be a (standard) way to distinct the 'internal' `INVENTORY` from the 'external' one inside the subquery, as used in an `UPDATE` statement. – Andriy M Mar 01 '11 at 13:30
  • @Andriy M: thanks so much for taking the time to help me , i will try to implement your solution and see how it works... – seb Mar 02 '11 at 02:08
  • @Andriy M well i just found out that the version of pervasive im using does not support INNER JOINS with UPDATES. DOH! – seb Mar 02 '11 at 03:20
  • @seb: I've dreaded such a conclusion since Dems's remark. Sorry. Rewrote the query to be Pervasive syntax-compliant (hopefully). Please check. – Andriy M Mar 02 '11 at 06:00
  • @Andriy M based on your input I have been able to get this to work, again , thanks a lot for your time in helping me! – seb Mar 03 '11 at 00:29