Re: sql join and conditional update

First of all, why do you want to "subtract zero from unmatched records"? 
Why update them at all?  Is it because if the record already has a 
negative number you want to change it to zero?

select * from dudstock
....+....1....+....2.
DSPROD       DSLQSTS 
  A             1.00 
  B             1.00-
  C     9,000,000.00 
  D           800.00 

select * from crt
....+....1....+....
CODE           QTY 
 A           20.00 
 C           40.00 

update dudstock
set dslqsts= 
  case when dslqsts-(select qty 
                     from crt 
                     where dsprod=code)>0
       then dslqsts-(select qty 
                     from crt 
                     where dsprod=code) 
       else 0 
  end 
where dsprod in (select dsprod 
                 from dudstock 
                 inner join crt 
                 on dsprod=code) 
2 rows updated in DUDSTOCK in QTEMP. 

select * from dudstock 
....+....1....+....2.
DSPROD       DSLQSTS 
  A              .00 
  B             1.00-
  C     8,999,960.00 
  D           800.00 


Rob Berendt