RE: SQL 400 Update with SubSelect

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