Update statement, which affects only columns, which would get a new value

[Norman Scheffler]

Hello,

I wrote an update trigger to detect the names of the updated columns of a table using the UPDATED(columnName) function.
In a stored procedure I wrote code to update user information with given parameters.

Update daUser
SET 
  strFirstName = @vstrFirstName,
  strLastName = @vstrLastName,
  strEMail = @vstrEMail
WHERE 
  uidUserGuid = @vuidUserGuid
 

My problem is, that this statement updates all the columns (strFirstName, strLastName and strEMail), but I dont want to update a column, if the old value matches the new value.
My first idea was:

UPDATE daUser
SET
  daUser.strFirstName = CASE WHEN daU.strFirstName <> @vstrFirstName THEN @vstrFirstName END,
  daUser.strLastName = CASE WHEN strLastName <> @vstrLastName THEN @vstrLastName END,
  strEMail = CASE WHEN strEMail = @vstrEmail THEN @vstrEMail END
FROM daUser daU
 
WHERE daU.uidUserGuid = @vuidUserGuid
 

Okay, the script above only updates columns with new values, if they are not like the old values.
But that's not the effect, I want, cause in the case, that the new value matches the old value, the script above will update the field with NULL value.
What I need is something like the following, but it doesn't works:

UPDATE daUser
SET
  CASE WHEN daUser.strFirstName <> @vstrFirstname THEN strFirstName = @vstrFirstName END,
  CASE WHEN daUser.strLastName <> @vstrLastName THEN strLastName = @vstrLastName END,
  CASE WHEN daUser.strEMail <> @vstrEMail THEN strEMail = @vstrEMail END
FROM daUser daU
WHERE daU.uidUserGuid = @vuidUserGuid
 

Can somebody help me to find a way?

Norman

[ck9663]

Will this work:

UPDATE daUser
SET
  daUser.strFirstName = isnull(CASE WHEN daU.strFirstName = @vstrFirstName THEN NULL END,daU.strFirstName),
  daUser.strLastName = isnuLL(CASE WHEN strLastName = @vstrLastName THEN NULL END,strLastName),
  strEMail = isnull(CASE WHEN strEMail = @vstrEmail THEN NULL END,strEMail)
FROM daUser daU
 
WHERE daU.uidUserGuid = @vuidUserGuid
 

Happy coding!

-- CK

[Norman Scheffler]

Will this work:

UPDATE daUser
SET
  daUser.strFirstName = isnull(CASE WHEN daU.strFirstName = @vstrFirstName THEN NULL END,daU.strFirstName),
  daUser.strLastName = isnuLL(CASE WHEN strLastName = @vstrLastName THEN NULL END,strLastName),
  strEMail = isnull(CASE WHEN strEMail = @vstrEmail THEN NULL END,strEMail)
FROM daUser daU
 
WHERE daU.uidUserGuid = @vuidUserGuid
 

Happy coding!

-- CK

Hello, hmm, your code "produces" the following error:
"None of the result expressions in a CASE specification can be NULL."


I think, I have to explain once more:

If the new value e.g. for e-mail matches the old value for a given user, the column should not been updated, cause I have to track only really updated columns in a trigger.
In this trigger I could compare DELETED.strFirstName with Inserted.strfirstName and so on, but there are n fields in different tables, NTEXT, TEXT and IMAGE columns too (not comparable in triggers). So in trigger I am only abe to check, if a column has been updated, but not how.
Thank you for answers!

Norman

[ck9663]

try this

UPDATE daUser
SET
  daUser.strFirstName = CASE WHEN daU.strFirstName <> @vstrFirstName THEN @vstrFirstName else strFirstName END,
  daUser.strLastName = CASE WHEN strLastName <> @vstrLastName THEN @vstrLastName else strLastName END,
  strEMail = CASE WHEN strEMail <> @vstrEmail THEN @vstrEmail else strEMail END
FROM daUser daU
WHERE daU.uidUserGuid = @vuidUserGuid
 

-- CK

[Norman Scheffler]

try this

UPDATE daUser
SET
  daUser.strFirstName = CASE WHEN daU.strFirstName <> @vstrFirstName THEN @vstrFirstName else strFirstName END,
  daUser.strLastName = CASE WHEN strLastName <> @vstrLastName THEN @vstrLastName else strLastName END,
  strEMail = CASE WHEN strEMail <> @vstrEmail THEN @vstrEmail else strEMail END
FROM daUser daU
WHERE daU.uidUserGuid = @vuidUserGuid
 

-- CK

Hello,

no, that code does not brings the result I want.
In the script above the fields are updated with a new value or the old value.
If I check, if a column was updated in a trigger (e.g. IF UPDATED(strFirstName) ... ), it is always true.

Is there another way?

Norman

[ck9663]

Option 1:

Handle it on the trigger. Compare values of INSERTED and DELETED tables.

Option 2:

Run the UPDATE, thrice and use WHERE

-- CK

[Norman Scheffler]

Hello,

that's my way:
1st I added a new column to the table daUser
(strDirtyColumns VARCHAR(200))
2nd in my stored procedure:

DECLARE @strDirtyColumns VARCHAR(100)
SET @strDirtyColumns = ''
-- 1st step: check dirty columns:
SELECT
@strDirtyColumns = CASE WHEN strfirstName = @strFirstName THEN '' ELSE ',strFirstName' END,
@strDirtyColumns = CASE WHEN strLastName = @vstrLastName THEN '' ELSE @strDirtyColumns + ',strLastName' END,
--...
--...
FROM daUser 
WHERE uidUserGuid = @vuidUserGuid
 
--2nd: I do the UPDATE wit the new column 'strDirtyColumns':
UPDATE daUser SET
strfirstname = @vstrfirstName,
strLastName = @vstrLastName,
-- ...
strDirtyColumns = @strDirtyColumns
WHERE uidUserGuid = @vuidUserGuid
 

It is not possible to compare NTEXT/TEXT/IMAGE fields from DELETED or INSERTED table in a trigger, that was my problem.

Thanks for your hints!

Norman

[ck9663]

If you can compare it in regular tables, you can compare it on the logical table.

-- CK