Update only if not null
-
Hi,
I have this update statement,
UPDATE products SET productdetail = @detail, productprice = @price WHERE productid = @id.
Sometimes the value of @price will be null. For those cases, I don't want to update the price, I want to update only the detail. Is it possible to do in one single statement. I can do two updates, update detail followed by update price if price not null. But I would like to know if there is any other way to do it.
Thanks.
-
UPDATE products
SET productdetail = @detail,
productprice = @price
WHERE productid = @id
and @Price is not null
-
No but I still want to update the detail even if the price is null. I think the above statement either updates both detail and price or updates neither.
-
Hello Senthil
if (@price is null)
UPDATE products
SET productdetail = @detail
WHERE productid = @id
else
UPDATE products
SET productdetail = @detail,
productprice = @price
WHERE productid = @id
and @Price is not null
Lucky -
This is what I initially thought of. Its part of a big stored procedure. I am just curious if I can do it in one statement, to reduce the clutter. I guess not. Thanks.
-
>I guess not.
Guess again
UPDATE products
SET productdetail = @detail,
--Set productprice equal to itself, if @price is null
productprice = IsNull(@price, productprice)
WHERE productid = @id
[Edit] Dang, too slow
-
UPDATE products
SET productdetail = @detail,
productprice = ISNULL(@price, productprice)
WHERE productid = @id.
_____________
Code for TallyGenerator
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply