Re: sql join and conditional update

i dont want to change unmatched records, but the way my code was heading i had 
to "deal" with them somehow, and subtracting zero would leave them unchanged.
  I have run your code and its just what i want.  The syntax you used for the 
inner join will help lots.
  Thanks.
  

rob@xxxxxxxxx wrote:
  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