CODEWITHSUNDEEP
c#sqlasp.netsql-servert-sql
J.W.
J.W.

Reputation: 18181

Best way to check whether a row has been updated in SQL

I have an update statement which updates a table. And there is a column that records the last modified time. If data in a specific row has not been changed, I don't want to change the last modified date time.

What is the best way to check whether an update statement will change the row of data or not.

Thanks,

Upvotes: 8

Views: 26901

Answers (8)

IsmailS
IsmailS

Reputation: 10863

If you are using sql 2005/2008 then you can do as follows in the stored procedure.

update  newTable 
set     readKey='1'
output  inserted.id,
        inserted.readKey as readKey,
        deleted.readKey as prevReadKey
into @tempTable
where id = '1111'

Then you can select from @tempTable to verify if the prevReadKey and readKey has similar value if both has similar value you can reset your last modified datetime.

This way you don't have to fire multiple queries on the table in the case when a value is actually changing. But yes in the case when the value is not changing, this will be firing two update statements where none is required. This should be OK if those cases are rare.

P.S. NOTE:- The query given might be syntactically wrong as it is not tested. But this is the way your problem can be solved. I have done it in following way using OUTPUT clause with Merge statement in one of my project and it can be done with update statement too. Here is the reference of OUTPUT Clause

Upvotes: 1

James Piggot
James Piggot

Reputation: 417

It depends on whether you have control of the data or not. Seb above is correct in saying you should check the old data against the new data before doing the update. But what if the data is not under your control?

Say you are a webservice being asked to do an update. Then the only way to check would be to query the existing data and compare it to the new data.

Don't know of any SQL functionality that would detect whether the update has actually changed any data or not.

There are ways in SQL to detect how many rows have been included in an update statement. Don't know of a way to detect whether an update statement actually changed any data, that would be interesting to know.

Upvotes: 1

Rob Schripsema
Rob Schripsema

Reputation: 4016

You COULD write an INSTEAD OF UPDATE trigger in T-SQL, where you could do what has been suggested above in the DAL layer -- compare the values in the existing record vs. the values in the update statement and either apply the update or not. You could use the Columns_Updated() function in the trigger to see if anything had been updated, and proceed accordingly.

It's not particularly efficient from the machine's point of view, but you could write it once and it would handle this situation no matter which application, stored procedure or other process was trying to update the record.

Upvotes: 0

BradC
BradC

Reputation: 39916

Sounds like you are going through a table and modifying some rows, then you want to go BACK through the table a second time and update the timestamp for the rows that were just changed.

Don't do it in two passes. Just update the date/time at the same time as you update whatever other columns you are changing:

UPDATE myTable
SET retailprice = wholesaleprice * 1.10,
    lastmodified = GetDate()
WHERE ...

Or are you issuing an update statement on ALL rows, but for most rows, it just sets it to the value it already has? Don't do that. Exclude those rows that wouldn't be modified in your where clause:

UPDATE myTable
SET retailprice = wholesaleprice * 1.10,
    lastmodified = GetDate()
WHERE retailprice <> wholesaleprice * 1.10

Upvotes: 2

brian-brazil
brian-brazil

Reputation: 34112

One way is to start a transaction, select the contents of the row and compare it to what you're going to update it to. If they don't match, then do the update and end the transaction. If they match, rollback the transaction.

Upvotes: 2

Ot&#225;vio D&#233;cio
Ot&#225;vio D&#233;cio

Reputation: 74250

That's when a DAL is handy. It keeps track of all colums so if none changed then I don't even send an UPDATE statement to the database.

Upvotes: 1

casperOne
casperOne

Reputation: 74530

If you want to do this preemptively, the only way I can think of that you will do this is to modify the WHERE clause of the update statement to compare the existing value vs the new value (for EVERY value). If ANY of them are not equal, then the update should take place.

Upvotes: 1

Seb
Seb

Reputation: 25147

Check the old vs. new data in your code instead of doing it in a query.

No need to bother the DB layer unnecessarily if data didn't change at all.

In short, if data didn't change, don't send the UPDATE statement.

Upvotes: 9

Related Questions