Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12345»»»

UPDATE when the values are the same Expand / Collapse
Author
Message
Posted Sunday, August 12, 2012 4:19 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, August 22, 2012 6:07 AM
Points: 5, Visits: 9
Does an UPDATE query overwrite existing values if the already existing value is the same as the one your updating with?
Post #1343937
Posted Sunday, August 12, 2012 6:25 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, January 15, 2013 11:11 AM
Points: 1,945, Visits: 2,782
Does an UPDATE query overwrite existing values if the already existing value is the same as the one your updating with?


Who cares? The point is that the table is correct after the update. I am going to guess what you re trying to ask is whether or not a row updated to its pre-update value will count to the total number of rows effected.

Yes, it will. SQL works with sets. Sets can be empty (zero elements), or any positive number of elements. The set of update rows is determined by the WHERE clause.

A smart optimizer will probably not actually do the redundant disk access. A comm9on trick is to use a CASE expression to get logic into the UPDATE:
UPDATE Foobar
SET kluub = CASE WHEN .. THEN .. ELSE kluub END
WHERE ..;



Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Post #1343950
Posted Sunday, August 12, 2012 8:14 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Saturday, April 12, 2014 9:02 PM
Points: 351, Visits: 887
I think what you mean is if you have a data set like this:
CREATE TABLE USERS ( TYPEID INT,USERNAME VARCHAR(30),)

INSERT VALUES (1,'JSMITH')
INSERT VALUES (2,'JDOE')
INSERT VALUES (1,'ASMITH')

and you do something like this:
UPDATE USERS
SET TYPEID = 3
WHERE TYPEID = 1

will the update statement update both JSMITH and ASMITH? The simple answer is yes, it will.
Post #1343952
Posted Sunday, August 12, 2012 10:18 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Sunday, April 13, 2014 9:51 PM
Points: 4,570, Visits: 8,315
CELKO (8/12/2012)
Does an UPDATE query overwrite existing values if the already existing value is the same as the one your updating with?


Who cares?


Triggers, for example. transaction logs. Users sitting in frount of the frozen UI waiting while the database finishes the useless activity.

Update dbo.Event
SET Status = 1
WHERE Eventdate < GETDATE()
and Status = 0

Without checking for the status value update will take forever on a big history table and add a big chunk to the Transaction Log file.
If there is a FOR UPDATE trigger it will be executed for all records having Eventdate in the past.

With Status = 0 added to the query it will actually update only recently added/processed records which have not been updated before. And trigger(s) will be run only for that smaller subset.

When I fixed similar query on one of production databases it removed 8GB of data added daily to the TRN file. Not to mention easing the pressure on the server, improving performance not only this but other systems having databases hosted on the same SQL Server.
Post #1343969
Posted Monday, August 13, 2012 1:50 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, August 22, 2012 6:07 AM
Points: 5, Visits: 9
Ask a simple yes/no question and get a few tangent replies, damn.

Its quite simple. You have a table as follows.

CREATE TABLE Test (
ID INT IDENTITY(1,1)
Name VARCHAR(100)
)

INSERT INTO Test (Name) VALUES ('Tom')
UPDATE Test SET Name = 'Tom' WHERE ID = 1

Does the update statement overwrite Tom even though Tom is already the value.
Post #1344001
Posted Monday, August 13, 2012 2:02 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 11:52 AM
Points: 41,530, Visits: 34,446
Why are you asking, and at what level of the engine are you interested in? (because the behaviour is interesting and complex when you get down to the deep internals)

At a high level, yes it will. The row qualifies for the update, so the row gets updated, it will count towards the rows affected and the row will appear in the inserted and deleted pseudo-tables in triggers (and in the OUTPUT clause)

When it comes to logging and to the actual modifications of the pages, it's more complex. It's not a simple yes/no question.




Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1344004
Posted Monday, August 13, 2012 3:35 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, August 22, 2012 6:07 AM
Points: 5, Visits: 9
Okay so getting a little more specific. Does the SQL Server edit the MDF file and overwrite the current value of that record or does it carry out a check to see if their the same to save writing to the HDD something that's already there.
Post #1344046
Posted Monday, August 13, 2012 3:59 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 11:52 AM
Points: 41,530, Visits: 34,446
Well... To start all changes are made in memory and not to disk, and the changes to the files on disk are done later (by a background process) at a page level, not a row level...

As far as I can tell, updating a row and not actually changing the values does not mark the page as dirty, meaning it won't be written back to disk when the checkpoint process runs.
I'm not saying it isn't ever marked dirty, but in my simple tests with a single row in a table, updating the name to itself did not appear to mark the page as dirty.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1344050
Posted Monday, August 13, 2012 4:04 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, August 22, 2012 6:07 AM
Points: 5, Visits: 9
Yeah sorry I appreciate its all done in memory but I was referring to the write back stage.

That's pretty much answered my question, cheers.
Post #1344052
Posted Monday, August 13, 2012 4:38 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 12:24 PM
Points: 4,828, Visits: 11,183
chris 76827 (8/13/2012)
Yeah sorry I appreciate its all done in memory but I was referring to the write back stage.

That's pretty much answered my question, cheers.


That does not mean that you should not do the check though:

update x
set y = 3
where y <> 3 or y is null

will run faster, in my experience, than

update x
set y = 3

especially if many of the rows are already 3.



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1344065
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse