Reputation: 583
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
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
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