RE: SQL 400 Update with SubSelect

["Haas, Matt" <Matt.Haas@xxxxxxxxxxx>]

You need to add EXISTS between the "where" and the "(". You also need to
specify the join criteria between "header" and "detail" in your
sub-query. You can do that either in the where clause of the sub-query
or by using JOIN in the from clause (for example: SELECT * FROM HEADER
JOIN DETAIL ON HEADER.KEY1=DETAIL.KEY1...).

Matt

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of
Shannon.Wolf@xxxxxxxxxx
Sent: Wednesday, July 20, 2005 3:04 PM
To: rpg400-l@xxxxxxxxxxxx
Subject: SQL 400 Update with SubSelect

I am attempting to update one table and using information from table a ,

as well as a table b in order to do so.

update HEADER set HEADER.FIELD1 = ' ' where (select * from header,detali

where HEADER.FIELD2 =  'A' AND
HEADER.FIELD3 = 'B' AND HEADER.FIELD4 = 'C' AND DETAIL.FIELD1 > 30)

Can someone please put me on the right track?

Thanks in advance

Shannon