By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,856 Members | 2,163 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,856 IT Pros & Developers. It's quick & easy.

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

P: 4
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.

Expand|Select|Wrap|Line Numbers
  1. Update daUser
  2. SET 
  3.   strFirstName = @vstrFirstName,
  4.   strLastName = @vstrLastName,
  5.   strEMail = @vstrEMail
  6. WHERE 
  7.   uidUserGuid = @vuidUserGuid
  8.  
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:
Expand|Select|Wrap|Line Numbers
  1. UPDATE daUser
  2. SET
  3.   daUser.strFirstName = CASE WHEN daU.strFirstName <> @vstrFirstName THEN @vstrFirstName END,
  4.   daUser.strLastName = CASE WHEN strLastName <> @vstrLastName THEN @vstrLastName END,
  5.   strEMail = CASE WHEN strEMail = @vstrEmail THEN @vstrEMail END
  6. FROM daUser daU
  7.  
  8. WHERE daU.uidUserGuid = @vuidUserGuid
  9.  
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:

Expand|Select|Wrap|Line Numbers
  1. UPDATE daUser
  2. SET
  3.   CASE WHEN daUser.strFirstName <> @vstrFirstname THEN strFirstName = @vstrFirstName END,
  4.   CASE WHEN daUser.strLastName <> @vstrLastName THEN strLastName = @vstrLastName END,
  5.   CASE WHEN daUser.strEMail <> @vstrEMail THEN strEMail = @vstrEMail END
  6. FROM daUser daU
  7. WHERE daU.uidUserGuid = @vuidUserGuid
  8.  
Can somebody help me to find a way?

Norman
Nov 16 '08 #1
Share this Question
Share on Google+
7 Replies


ck9663
Expert 2.5K+
P: 2,878
Will this work:


Expand|Select|Wrap|Line Numbers
  1. UPDATE daUser
  2. SET
  3.   daUser.strFirstName = isnull(CASE WHEN daU.strFirstName = @vstrFirstName THEN NULL END,daU.strFirstName),
  4.   daUser.strLastName = isnuLL(CASE WHEN strLastName = @vstrLastName THEN NULL END,strLastName),
  5.   strEMail = isnull(CASE WHEN strEMail = @vstrEmail THEN NULL END,strEMail)
  6. FROM daUser daU
  7.  
  8. WHERE daU.uidUserGuid = @vuidUserGuid
  9.  

Happy coding!

-- CK
Nov 16 '08 #2

P: 4
Will this work:


Expand|Select|Wrap|Line Numbers
  1. UPDATE daUser
  2. SET
  3.   daUser.strFirstName = isnull(CASE WHEN daU.strFirstName = @vstrFirstName THEN NULL END,daU.strFirstName),
  4.   daUser.strLastName = isnuLL(CASE WHEN strLastName = @vstrLastName THEN NULL END,strLastName),
  5.   strEMail = isnull(CASE WHEN strEMail = @vstrEmail THEN NULL END,strEMail)
  6. FROM daUser daU
  7.  
  8. WHERE daU.uidUserGuid = @vuidUserGuid
  9.  

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
Nov 16 '08 #3

ck9663
Expert 2.5K+
P: 2,878
try this

Expand|Select|Wrap|Line Numbers
  1. UPDATE daUser
  2. SET
  3.   daUser.strFirstName = CASE WHEN daU.strFirstName <> @vstrFirstName THEN @vstrFirstName else strFirstName END,
  4.   daUser.strLastName = CASE WHEN strLastName <> @vstrLastName THEN @vstrLastName else strLastName END,
  5.   strEMail = CASE WHEN strEMail <> @vstrEmail THEN @vstrEmail else strEMail END
  6. FROM daUser daU
  7. WHERE daU.uidUserGuid = @vuidUserGuid
  8.  
-- CK
Nov 16 '08 #4

P: 4
try this

Expand|Select|Wrap|Line Numbers
  1. UPDATE daUser
  2. SET
  3.   daUser.strFirstName = CASE WHEN daU.strFirstName <> @vstrFirstName THEN @vstrFirstName else strFirstName END,
  4.   daUser.strLastName = CASE WHEN strLastName <> @vstrLastName THEN @vstrLastName else strLastName END,
  5.   strEMail = CASE WHEN strEMail <> @vstrEmail THEN @vstrEmail else strEMail END
  6. FROM daUser daU
  7. WHERE daU.uidUserGuid = @vuidUserGuid
  8.  
-- 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
Nov 17 '08 #5

ck9663
Expert 2.5K+
P: 2,878
Option 1:

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

Option 2:

Run the UPDATE, thrice and use WHERE

-- CK
Nov 17 '08 #6

P: 4
Hello,

that's my way:
1st I added a new column to the table daUser
(strDirtyColumns VARCHAR(200))
2nd in my stored procedure:
Expand|Select|Wrap|Line Numbers
  1. DECLARE @strDirtyColumns VARCHAR(100)
  2. SET @strDirtyColumns = ''
  3. -- 1st step: check dirty columns:
  4. SELECT
  5. @strDirtyColumns = CASE WHEN strfirstName = @strFirstName THEN '' ELSE ',strFirstName' END,
  6. @strDirtyColumns = CASE WHEN strLastName = @vstrLastName THEN '' ELSE @strDirtyColumns + ',strLastName' END,
  7. --...
  8. --...
  9. FROM daUser 
  10. WHERE uidUserGuid = @vuidUserGuid
  11.  
  12. --2nd: I do the UPDATE wit the new column 'strDirtyColumns':
  13. UPDATE daUser SET
  14. strfirstname = @vstrfirstName,
  15. strLastName = @vstrLastName,
  16. -- ...
  17. strDirtyColumns = @strDirtyColumns
  18. WHERE uidUserGuid = @vuidUserGuid
  19.  
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
Nov 18 '08 #7

ck9663
Expert 2.5K+
P: 2,878
If you can compare it in regular tables, you can compare it on the logical table.

-- CK
Nov 18 '08 #8

Post your reply

Sign in to post your reply or Sign up for a free account.