• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 5990
  • Last Modified:

AS400 SQL - Updating records from one file with data from another file

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?

Thanks

JAmes
0
ilikeulike
Asked:
ilikeulike
  • 4
  • 2
2 Solutions
 
momi_sabagCommented:
try

UPDATE TSTT2F1/INP60   t1
SET SCSU60 = (select STDCOST from FROM JAMES/NEWSTDCOST
                     WHERE ABCPART=t1.PNUM60)
WHERE CONO60='NC'
AND STRC60='IN'
AND EXISTS (SELECT ABCPART
                     FROM JAMES/NEWSTDCOST
                     WHERE ABCPART=t1.PNUM60)
0
 
ilikeulikeAuthor Commented:
Hi Momi_sabag
Thanks for your reply. I have tried that but get the error:
Token / was not valid. Valid tokens: ) UNION, with the cursor highligting the / on the "FROM JAMES/NEWSTDCOST"

Just a question, but what does the t1 mean?

Sorry to sound stupid?

Thanks

James
0
 
momi_sabagCommented:
t1 is just an alias for the table so that you could use it in the subquery
i'm not as as400 guy, so don't know what to tell you about the / problem

does this query works?
SELECT ABCPART
                     FROM JAMES/NEWSTDCOST
0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

 
ilikeulikeAuthor Commented:
Yes, the select query worked fine.
0
 
ilikeulikeAuthor Commented:
I have retried as i had a problem in my code. I now get :
UPDATE tstt2f1/inp60 t1     SET SCSU60 = (SELECT STDCOST FROM james/
newstdcost  WHERE ABCPART = t1.PNUM60)                              
                                                                     
WHERE CONO60='NC' and STRC60='IN'                                    
                                                                     
AND EXISTS (SELECT ABCPART FROM james/newstdcost                    
WHERE ABCPART = t1.PNUM60)
 
Result of SELECT more than one row.  

Any thoughts?

Thanks

James
0
 
tliottaCommented:
SET SCSU60 = (SELECT STDCOST FROM james/newstdcost  WHERE ABCPART = t1.PNUM60)

If more than one row is being selected, it means that ABCPART is found more than once in james/newstdcost. In order to narrow it down, you'll need to make the WHERE clause more selective. You probably need to include both CONO60 and STRC60 so that you get the cost for that part only from that Company and that Stock Room.

And if that doesn't do it, you need to verify that there is in fact only one row for each Company/Stock Room/Part combination.

Tom
0
 
ilikeulikeAuthor Commented:
Hi Guys,  thanks for all your help. The solution worked a treat (thanks momi_sabag) once i had removed a duplicate entry from my data (thanks Tom)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now