Re: SQL 400 Update with SubSelect

 <Shannon.Wolf@...> writes:

> 
> 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 

Hi Shannon,
           Hope the below examples help....


update invbal set
   dept = (select coalesce( invmst.dept, 0 ) from invmst
           where invmst.item = invbal.item)


To leave the department as is when no match is found, you use this:

   update invbal set
   dept = (select coalesce( invmst.dept, invbal.dept ) from invmst     
           where invmst.item = invbal.item)


update invbal set
   dept = (select coalesce( invmst.dept, 0 ) from invmst
           where invmst.item = invbal.item),
   class = (select coalesce( invmst.class, 0 ) from invmst
           where invmst.item = invbal.item)