Re: SQL 400 Update with SubSelect

Very messed up.
For example, what ties HEADER to DETAIL?
What should happen if there are no detail records?
What should happen if there are multiple detail records for each header 
record?

select * from rob/header 
....+....1....+....2....+....3..
FIELD1  FIELD2  FIELD3  DEADBEAT
  1       1       1 
  2       2       2 
  3       3       3 
********  End of data  ******** 

select * from rob/detail 
....+....1....+....2.
FIELDA        FIELDB 
  1            25.00 
  1            15.00 
********  End of data

update rob/header H 
set deadbeat = 
Case 
When (select sum(d.fieldb) 
      from rob/detail d 
       where h.field1=d.fielda)>30
  Then 'Y' 
else 
  ' ' 
end 

select * from rob/header
....+....1....+....2....+....3..
FIELD1  FIELD2  FIELD3  DEADBEAT
  1       1       1        Y 
  2       2       2 
  3       3       3 
********  End of data  ******** 


Rob Berendt