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

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.

ASKER CERTIFIED SOLUTION

regsworld

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

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

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.

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

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.