CODEWITHSUNDEEP
t-sqlsql-updatecase
korrowan
korrowan

Reputation: 583

Case when to update multiple columns at once

I have never used Case before and am not sure how to do this but we have had some issues doing a wholesale update because of addresses changing that are now coming in the data that did not used to. I am trying to use it as an if then statement but I am sure that I am doing wrong. I need to update addresses when a field is either empty or null. 

UPDATE    defendants_ALL_Fixed
SET              tiffAdd = tiffAdds.Add1, tiffZip = tiffAdds.Zip, tiffCity = tiffAdds.City, tiffState = tiffAdds.State
FROM         defendants_ALL_Fixed INNER JOIN
                      tiffAdds ON defendants_ALL_Fixed.tiff = tiffAdds.Tiff
WHERE     (defendants_ALL_Fixed.tiff = tiffAdds.Tiff)

I have tried wrapping that into a case statement such as:

SELECT
  a.tiffAdd
, CASE WHEN a.tiffAdd = '' THEN

UPDATE    defendants_ALL_Fixed
SET              tiffAdd = tiffAdds.Add1, tiffZip = tiffAdds.Zip, tiffCity = tiffAdds.City, tiffState = tiffAdds.State
FROM         defendants_ALL_Fixed INNER JOIN
                      tiffAdds ON defendants_ALL_Fixed.tiff = tiffAdds.Tiff
WHERE     (defendants_ALL_Fixed.tiff = tiffAdds.Tiff)

END
FROM defendants_ALL_Fixed a

Which did not work and I understand why it does not work but have no idea where to go from here and any help would be great. We only use this once a month to update data (about 1.5 million records) and the resources it uses is not at all relevant at this point.

Thanks.

Upvotes: 1

Views: 1385

Answers (2)

Rafał
Rafał

Reputation: 590

Why dont you use merge instead? Use merge on every each field, checking if the value is not null or empty. I think it wpould be the best (used it on my own solution).

Upvotes: 0

HeavenCore
HeavenCore

Reputation: 7683

Assuming i am understanding your question (i.e. only update defendants_ALL_Fixed fields with tiffAdds when the defendants_ALL_Fixed fields are blank), you could do this as follows (sticking with case):

UPDATE  defendants_ALL_Fixed
SET     tiffAdd = CASE WHEN LTRIM(RTRIM(ISNULL(tiffAdd, ''))) = '' THEN tiffAdds.Add1
                       ELSE tiffAdd
                  END ,
        tiffZip = CASE WHEN LTRIM(RTRIM(ISNULL(tiffZip, ''))) = '' THEN tiffAdds.Zip
                       ELSE tiffZip
                  END ,
        tiffCity = CASE WHEN LTRIM(RTRIM(ISNULL(tiffCity, ''))) = '' THEN tiffAdds.City
                        ELSE tiffCity
                   END ,
        tiffState = CASE WHEN LTRIM(RTRIM(ISNULL(tiffState, ''))) = '' THEN tiffAdds.State
                         ELSE tiffState
                    END
FROM    defendants_ALL_Fixed
        INNER JOIN tiffAdds ON defendants_ALL_Fixed.tiff = tiffAdds.Tiff

A simpler (or rather, easier to read) option is to do simple update statements based on a WHERE clause (one update statement per column):

UPDATE  defendants_ALL_Fixed
SET     tiffAdd = tiffAdds.Add1
FROM    defendants_ALL_Fixed
        INNER JOIN tiffAdds ON defendants_ALL_Fixed.tiff = tiffAdds.Tiff
WHERE   ( LTRIM(RTRIM(ISNULL(tiffAdd, ''))) = '' )

Edit 1: based on your comment, That would suggest you can just update all columns where tiffAdd = ''? i.e. 

UPDATE  defendants_ALL_Fixed
SET     tiffAdd = tiffAdds.Add1 ,
        tiffZip = tiffAdds.Zip ,
        tiffCity = tiffAdds.City ,
        tiffState = tiffAdds.State
FROM    defendants_ALL_Fixed
        INNER JOIN tiffAdds ON defendants_ALL_Fixed.tiff = tiffAdds.Tiff
WHERE   LTRIM(RTRIM(ISNULL(tiffAdd, ''))) = ''

Upvotes: 2

Related Questions