Avatar of WaldaInc
WaldaInc
 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.

UPDATE Contact
SET
	PostalAddressLine1 = @AddressLine1,
	CityID = @CityID,
	TerritoryID = @StateID,
	PostalZipCode = @ZipCode
WHERE EntityID = 1
AND EntityValueID = @PersonID

Open in new window

Microsoft SQL ServerSQL

Avatar of undefined
Last Comment
WaldaInc

8/22/2022 - Mon
SaedSalman

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 Contact
SET
      PostalAddressLine1 = @AddressLine1,
      CityID = COALESCE(@CityID, CityID) ,
      TerritoryID = @StateID,
      PostalZipCode = @ZipCode
WHERE EntityID = 1
AND EntityValueID = @PersonID

Open in new window

WaldaInc

ASKER
I'm getting an Invalid Syntax for your code.

Doesn't like the ==null nor the last @AddressLine1.

Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
ASKER CERTIFIED SOLUTION
regsworld

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.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
SaedSalman

> I'm getting an Invalid Syntax for your code.

AddressLine1 must be declared as SqlParameter.

AngelIII posted better solution :)
WaldaInc

ASKER
What if the values are not NULL but an empty string?
regsworld


Try the above code for the empty strings
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Guy Hengel [angelIII / a3]

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

Open in new window

ChristopherDunn

Use regsworld's code but use SET instead of SELECT for the first 4 lines or use angellll's but replace ISNULL with NULLIF
WaldaInc

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.

Your help has saved me hundreds of hours of internet surfing.
fblack61
regsworld


The code is pre-tested, and it works :)
Sharath S

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

Open in new window

Sharath S

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

Open in new window

⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
WaldaInc

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.