Re: sql join and conditional update

[rob@xxxxxxxxx]

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