UPDATE - conditional SET

[GZ1]

Hi all,
Is there a way of changing my update query :

UPDATE Table1 SET Table1.AValue = Table1.AValue - Table2.Value FROM
Table1,Table2 WHERE ((Table1.ID = Table2.IRecNo) AND (Table2.Type =
1))

in order to action the following :

With the SET , if Table1.Avalue goes negative then set it to Zero.
As in could I check if Table2.Value is greater then set Table1.Avalue
to 0 within this expression, or do I need a new approach?

Cheers.

[Albe V°]

"GZ1" <Gary.ki...@promissoftware.co.uk> ha scritto nel messaggio
news:aa815e6f.02102...@posting.google.com...

> Hi all,
> Is there a way of changing my update query :
>
> UPDATE Table1 SET Table1.AValue = Table1.AValue - Table2.Value FROM
> Table1,Table2 WHERE ((Table1.ID = Table2.IRecNo) AND (Table2.Type =
> 1))
>
> in order to action the following :
>
> With the SET , if Table1.Avalue goes negative then set it to Zero.

UPDATE Table1
SET Table1.AValue = (CASE
when Table1.AValue - Table2.Value<0 then 0
else Table1.AValue - Table2.Value
end)
from.....

Bye

Alberto

PS: check syntax, I wrote it too quickly...

[--CELKO--]

Gary.ki...@promissoftware.co.uk (GZ1) wrote in message news:<aa815e6f.02102...@posting.google.com>...

UPDATE Table1
SET value
= (SELECT
CASE WHEN Table1.value - Table2.value <= 0
THEN 0
ELSE Table1.value - Table2.value END
FROM Table2);

Avoid the proprietary, non-portable UPDATE.. FROM syntax. Harder to
read, but probably faster:

UPDATE Table1
SET a_value
= (SELECT 1- (SIGN (1 - SIGN(Table2.value - Table1.value)))
* (Table1.value - Table2.value)
FROM Table2);

This is hard to see, but here is the algebra:

1- (SIGN (1 - (0))) = 1- SIGN(1) = 0

1- (SIGN (1 - (-1))) = 1- SIGN(2) = 0

1- (SIGN (1 - (+1))) = 1- SIGN(0) = 1