UPDATE statement!

[MrProgrammer]

Hi,
I have 2 tables defined like below;

PreviousData
{ P_Tel_no number(10),
P_Counter number(8),
......
......
}

MonthlyData
{ M_Tel_no number(10),
M_Counter number(8),
M_Increase number(8)
}


MainData contains previous month's information and MonthlyData contains last month's values. M_Tel_no and M_Conter fileds are not empty but M_Increase is. And I want to set this filed to the difference between M_Counter and P_Counter. MonthlyData can contain new M_Tel_no value which doesn't exist in PreviousData. For these records, I want to assume that previous counter (P_Counter) is zero. What SQL sentence can do this?

I tried this;

UPDATE MonthlyData m SET m.M_Increase = (SELECT m.M_Counter - p.P_Counter FROM PreviousData p where m.M_Tel_no=p.P_Tel_no);

This works for numbers that exist in both tables but not for numbers which only exist in MonthlyData table.

For new M_Tel_no values, M_Increase is assigned NULL...

 

[LFCfan]

UPDATE MonthlyData m SET m.M_Increase = (SELECT m.M_Counter - ISNULL(p.P_Counter, 0) FROM PreviousData p where m.M_Tel_no=p.P_Tel_no);

ISNULL(<expression1>, <expression2>) will return expression1 if expression1 is NOT null, and will return expression2 if expression2 IS null

Hope this helps!