SQL statement needs correcting 1

[tomkonec]

I have successfully executed the following SQL statement in a Transoft U/SQL environment (on SCO Open Server using C-ISAM).

----------------------------------------------------
select sop_price_list.price_list,
sop_price_list.product_code,
sop_price_list.price,
st_stock.warehouse,
st_stock.product,
st_stock.description

FROM cs3.SOP_PRICE_LIST SOP_PRICE_LIST, cs3.ST_STOCK ST_STOCK

WHERE SOP_PRICE_LIST.PRODUCT_CODE = ST_STOCK.PRODUCT
AND ((analysis_a = 'GOBO') AND (analysis_c = 'METAL')
AND (warehouse = '60' or warehouse = '70')
AND (price_list = 'STD')
AND (sop_price_list.price = '6.00'))
----------------------------------------------------

Now I want to update a particular field using the UPDATE command however I'm sure that I have got this statement wrong as it is telling me that ST_STOCK is not recognised

----------------------------------------------------

update SOP_PRICE_LIST

set sop_price_list.price = '6.75'

WHERE SOP_PRICE_LIST.PRODUCT_CODE = ST_STOCK.PRODUCT
AND ((st_stock.analysis_a = 'GOBO') AND (st_stock.analysis_c = 'METAL')
AND (st_stock.warehouse = '60' or st_stock.warehouse = '70')
AND (sop_price_list.price_list = 'STD')
AND (sop_price_list.price = '6.00'))

----------------------------------------------------

Could someone tell me where I am going wrong. Thanks. Tom

 

[PHV]

Something like this ?
update SOP_PRICE_LIST
set sop_price_list.price = '6.75'
WHERE SOP_PRICE_LIST.PRODUCT_CODE IN
(SELECT ST_STOCK.PRODUCT FROM st_stock
WHERE st_stock.analysis_a='GOBO'
AND st_stock.analysis_c='METAL'
AND st_stock.warehouse IN('60','70')
)
AND sop_price_list.price_list='STD'
AND sop_price_list.price='6.00'

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884