asked on
SQL Syntax - Dynamic Update Statement
Hi.
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.
I hope I am explaiing myself clearly.
Thanks in advance for any and all comments.
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.
I hope I am explaiing myself clearly.
Thanks in advance for any and all comments.
UPDATE Contact
SET
PostalAddressLine1 = @AddressLine1,
CityID = @CityID,
TerritoryID = @StateID,
PostalZipCode = @ZipCode
WHERE EntityID = 1
AND EntityValueID = @PersonID
Microsoft SQL ServerSQL
Last Comment
WaldaInc
if the variable @CityID is NULL, you could do like this, retaining the column value:
UPDATE Contact
SET
PostalAddressLine1 = @AddressLine1,
CityID = COALESCE(@CityID, CityID) ,
TerritoryID = @StateID,
PostalZipCode = @ZipCode
WHERE EntityID = 1
AND EntityValueID = @PersonID
ASKER
I'm getting an Invalid Syntax for your code.
Doesn't like the ==null nor the last @AddressLine1.
Doesn't like the ==null nor the last @AddressLine1.
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
> I'm getting an Invalid Syntax for your code.
AddressLine1 must be declared as SqlParameter.
AngelIII posted better solution :)
AddressLine1 must be declared as SqlParameter.
AngelIII posted better solution :)
ASKER
What if the values are not NULL but an empty string?
Try the above code for the empty strings
like regsworld posted... or inline:
UPDATE Contact
SET
PostalAddressLine1 = @AddressLine1,
CityID = COALESCE( ISNULL( RTRIM(LTRIM(@CityID)), ''), CityID) ,
TerritoryID = @StateID,
PostalZipCode = @ZipCode
WHERE EntityID = 1
AND EntityValueID = @PersonID
Use regsworld's code but use SET instead of SELECT for the first 4 lines or use angellll's but replace ISNULL with NULLIF
ASKER
Ok, I think either regsworld aor angelll will work.
It will take me a day or two to finish the stored proc and then test it. At that time I'll award the points.
Thanks again.
It will take me a day or two to finish the stored proc and then test it. At that time I'll award the points.
Thanks again.
The code is pre-tested, and it works :)
you can try with CASE statement also.
UPDATE Contact
SET
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 = 1
AND EntityValueID = @PersonID
extra comma in my last post
UPDATE Contact
SET
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 = 1
AND EntityValueID = @PersonID
ASKER
Sorry for the delay in accepting this solution. I thought I did this a few days ago, but maybe that was just a dream.
Anyways, thanks for everyones suggestions. I used regsworld example so that's who I awarded the points to.
Anyways, thanks for everyones suggestions. I used regsworld example so that's who I awarded the points to.
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?