AS400 SQL - Updating records from one file with data from another file
Posted on 2012-03-21
Hi there, can anybody help me with an SQL problem i am having on our iseries?
I am trying to update the std cost of a range of parts.
The file is TSTT2F1/INP60, and the Stockroom is STRC60, the Company is NC and the Standard Cost is SCSU60.
I have created a file with the new prices (JAMES/NEWSTDCOST), and uploaded it to the AS400 (see sample data below)
CONO60 STRC60 ABCPART STDCOST
NC IN 3680 24.26
NC IN 3870 27.13
NC IN 3871 22.57
but I am not sure how to word the SQL statement to pick up all the different prices for each part. The parts may or may not exist in INP60 so i need to pick up the data for those that do.
The statement I have tried unsuccessfully is :
UPDATE TSTT2F1/INP60 SET SCSU60 = 'STDCOST' WHERE CONO60='NC' AND STRC60='IN' AND EXISTS (SELECT ABCPART FROM JAMES/NEWSTDCOST WHERE ABCPART=PNUM60 AND SCSU='STDCOST')
VALUE FOR COLUMN OR VARIABLE SCSU60 NOT COMPATIBLE.
Hopefully you can help me with this?