CODEWITHSUNDEEP
sqlsql-server
Robert
Robert

Reputation: 3543

SQL choose column to update inside CASE condition

I have ArticlePhotos table and each Article can have N photos. I am writing a trigger which will reflect changes made in ArticlePhotos table to create a flat hierarhy inside Articles table for faster querying to avoid joins when listing several articles.

Is there a way to select column to update in some sort of CASE clause instead writing update clause for each desired type

DECLARE @FrontPagePhotoTypeId INT = 1; 
DECLARE @MainArticlePhotoTypeId INT = 2; 
DECLARE @FacebookPhotoType INT = 4; 


-- all deleted photo records should be set as null in Articles table (flat hierarchy)

-- delete frontpage photo
UPDATE Articles 
SET Articles.FrontPagePhoto = NULL <-- can this be written as a case by PhotoTypeId
FROM Articles 
INNER JOIN deleted 
ON Articles.id = deleted.ArticleId WHERE deleted.PhotoTypeId = @FrontPagePhotoTypeId 

-- delete Main Article Photo
UPDATE Articles 
SET Articles.MainArticlePhoto = NULL
FROM Articles 
INNER JOIN deleted 
ON Articles.id = deleted.ArticleId WHERE deleted.PhotoTypeId = @MainArticlePhotoTypeId 

-- delete facebook
UPDATE Articles 
SET Articles.FacebookPhoto = NULL
FROM Articles 
INNER JOIN deleted 
ON Articles.id = deleted.ArticleId WHERE deleted.PhotoTypeId = @FacebookPhotoType

Upvotes: 1

Views: 49

Answers (1)

Jayasurya Satheesh
Jayasurya Satheesh

Reputation: 8033

The above query can be re-written as a single update as below

DECLARE @FrontPagePhotoTypeId INT = 1; 
DECLARE @MainArticlePhotoTypeId INT = 2; 
DECLARE @FacebookPhotoType INT = 4; 

UPDATE Articles 
SET 
    FrontPagePhoto = case when deleted.PhotoTypeId = @FrontPagePhotoTypeId
                                        then null
                                    else Articles.FrontPagePhoto end,
    MainArticlePhoto = case when deleted.PhotoTypeId = @MainArticlePhotoTypeId 
                                        then null
                                    else Articles.MainArticlePhoto end,
    FacebookPhoto = case when deleted.PhotoTypeId = @FacebookPhotoType 
                                        then null
                                    else Articles.FacebookPhoto end                             
FROM Articles 
INNER JOIN deleted 
ON Articles.id = deleted.ArticleId

Upvotes: 2

Related Questions