Can I update several columns by CASE expressions?

Hi, all here,

I am having a question-is it possible to update several columns using CASE expression in SQL language? like if I wanna set each CASE for each column.

Thanks a lot in advance for any help and guidance.

I think so. Have you tried something and it didn't work? A CASE expression just returns a scalar value, so you can do:

update tablename
set column1 = case when .... end,
column2 = case when ... end,

If you want. If the case statements are completely independent of one another this might be the best way to do something, but if they are from the same data set then you might want to use a join, and possibly a derived table.

Post an example and someone will give you an idea

|||Yes, you can.
Few times, i had to create Stored Procedures that work on just one table and all fields did not allow "NULL" values.
E.g.)

CREATE TABLE NewsTitle (
newstitle_id INT IDENTITY PRIMARY KEY
, title NVARCHAR(512)
, page SMALLINT NOT NULL
, media SMALLINT NOT NULL
)

If you would like to create a simple Sproc that will update a record in that table you might do something like(using CASE) the following:

CREATE PROCEDURE UpdateNewsTitle
@.id INT
, @.title NVARCHAR(512)
, @.page SMALLINT
, @.media SMALLINT
AS
BEGIN
UPDATE NewsTitle
SET title = CASE WHEN @.title IS NULL THEN title ELSE @.title END
, page = CASE WHEN @.page IS NULL THEN page ELSE @.page END
, media = CASE WHEN @.media IS NULL THEN media ELSE @.media END
WHERE newstitle_id = @.id
END
GO

But if you are just checking if passed argument is NULL or not and, if argument is not null then try to use that value, you can use COALESCE function to rid of CASE statement like the following:

ALTER PROCEDURE UpdateNewsTitle
@.id INT
, @.title NVARCHAR(512)
, @.page SMALLINT
, @.media SMALLINT
AS
BEGIN
UPDATE NewsTitle
SET title = COALESCE(@.title, title)
, page = COALESCE(@.page, page)
, media = COALESCE(@.media, media)
WHERE newstitle_id = @.id
END
GO

If you are not familiar with COALESCE, you can look it up on BOL(Books Online). In short, COALESCE returns FIRST non-null value.