f4301 sql update problem

golfok

Member
I need to update via an sql statement, the f4301 from the f4311 file. The fields I need to update in the f4301, is phexp1 and phtxa1, from f4311 fileds pdexp1 and pdtxa1.

I can run a select sql, with an inner join and all works fine. I guess you cannot use a join for an update. Noone in my AS/400 shop has written a SQL to update from one file to another.

If anyone can help, give me the update statement, we would really appreciate it.

Thanks
 
This is one way..

UPDATE PRODDTA/F4301 SET

PHEXP1=(SELECT PHEXP1 FROM PRODDTA/F4311 WHERE PDDCTO=PHDCTO AND PDDOCO=PHDOCO AND PDKCOO=PHKCOO AND PDLNID=1000)

, PHTXA1=(SELECT PHTXA1 FROM PRODDTA/F4311 WHERE PDDCTO=PHDCTO AND PDDOCO=PHDOCO AND PDKCOO=PHKCOO AND PDLNID=1000)

WHERE
PHDCTO='OP' AND PHDOCO=123 AND PHKCOO='00000'

Ugly, but i can't remember the other way where you need only one select statement for F4311. The important thing to remember is to ensure the select statement for F4311 only picks up one record

If you need to update multiple records and do not want to use a line number of F4311 you could try

UPDATE PRODDTA/F4301 SET

PHEXP1=(SELECT UNIQUE PHEXP1 FROM PRODDTA/F4311 WHERE PDDCTO=PHDCTO AND PDDOCO=PHDOCO AND PDKCOO=PHKCOO)

, PHTXA1=(SELECT UNIQUE PHTXA1 FROM PRODDTA/F4311 WHERE PDDCTO=PHDCTO AND PDDOCO=PHDOCO AND PDKCOO=PHKCOO)

WHERE
PHDCTO='OP' AND PHDOCO=123 AND PHKCOO='00000'

This could fail, though, if you have several lines on your PO:s with different values in PDTXA1 or PDEXP1 (because it will retrieve each unique value once)


I am sure there are better ways of doing this though..
 
Of course it's not UNIQUE but DISTINCT, sorry :)

UPDATE PRODDTA/F4301 SET

PHEXP1=(SELECT DISTINCT PHEXP1 FROM PRODDTA/F4311 WHERE PDDCTO=PHDCTO AND PDDOCO=PHDOCO AND PDKCOO=PHKCOO)

, PHTXA1=(SELECT DISTINCT PHTXA1 FROM PRODDTA/F4311 WHERE PDDCTO=PHDCTO AND PDDOCO=PHDOCO AND PDKCOO=PHKCOO)

WHERE
PHDCTO='OP' AND PHDOCO=123 AND PHKCOO='00000'
 
UPDATE f4301 a SET (phexp1, pxtxa1) =
(SELECT pdexp1,pdtxa1
FROM f4311 b
WHERE a.YourJoinField = b.YourJoinField)
WHERE EXISTS
(SELECT 1
FROM f4311 b
WHERE a.YourJoinField = b.YourJoinField)
 
That's just beautiful
smile.gif
 
if multiple values are retrieved by the SELECT statement ...update will fail

you have to be sure that the SELECT will bring a UNIQUE value. Use MIN, MAX, SUM (for numeric)

regards
 
Back
Top