I am processing a very large text file, trying to update columns for exising members. However, somtimes the text file contains blank values and I don't want to overwrite values that exist with blank values. So, if the values (from text file) are blank, we don't update that column.
The code snippet below shows a partial update (there will be more columns, but if I can get an example with these, I can do the rest).
Notice the SET portion. Any of those values (@CityID, @AddressLine1, @ZipCode) could be blank and if it is, I don't want to include that line in the SET.
How do i write an UPDATE that looks at each variable (@CityID etc..) and determines if we should put it in the UPDATE statement.
UPDATE Contact
SET
PostalAddressLine1 = (@AddressLine1==null ? PostalAddressLine1: @AddressLine1),
CityID = @CityID, // do the same for the rest
TerritoryID = @StateID,
PostalZipCode = @ZipCode
WHERE EntityID = 1
AND EntityValueID = @PersonID
does this help?
Guy Hengel [angelIII / a3]
if the variable @CityID is NULL, you could do like this, retaining the column value:
UPDATE ContactSET PostalAddressLine1 = CASE WHEN @AddressLine1 IS NULL OR RTRIM(LTRIM(@AddressLine1)) = '' THEN PostalAddressLine1 ELSE @AddressLine1 END, CityID = CASE WHEN @CityID IS NULL OR RTRIM(LTRIM(@CityID)) = '' THEN CityID ELSE @CityID END, TerritoryID = CASE WHEN @StateID IS NULL OR RTRIM(LTRIM(@StateID)) = '' THEN TerritoryID ELSE @StateID END, PostalZipCode = CASE WHEN @ZipCode IS NULL OR RTRIM(LTRIM(@ZipCode)) = '' THEN PostalZipCode ELSE @ZipCode END,WHERE EntityID = 1AND EntityValueID = @PersonID
UPDATE ContactSET PostalAddressLine1 = CASE WHEN @AddressLine1 IS NULL OR RTRIM(LTRIM(@AddressLine1)) = '' THEN PostalAddressLine1 ELSE @AddressLine1 END, CityID = CASE WHEN @CityID IS NULL OR RTRIM(LTRIM(@CityID)) = '' THEN CityID ELSE @CityID END, TerritoryID = CASE WHEN @StateID IS NULL OR RTRIM(LTRIM(@StateID)) = '' THEN TerritoryID ELSE @StateID END, PostalZipCode = CASE WHEN @ZipCode IS NULL OR RTRIM(LTRIM(@ZipCode)) = '' THEN PostalZipCode ELSE @ZipCode ENDWHERE EntityID = 1AND EntityValueID = @PersonID
SET
PostalAddressLine1 = (@AddressLine1==null ? PostalAddressLine1: @AddressLine1),
CityID = @CityID, // do the same for the rest
TerritoryID = @StateID,
PostalZipCode = @ZipCode
WHERE EntityID = 1
AND EntityValueID = @PersonID
does this help?