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

Question

I have an update statement that updates a table. And there is a column in which the last modified time is recorded. If the data in a particular row has not been changed, I do not want to change the last changed time.

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

Thanks,

Answer 1

Check the old or new data in the code instead of doing it in the query.

No need to disturb the database level unnecessarily if the data does not change at all.

In short, if the data has not changed, do not send the UPDATE statement.

Answer 2

One way is to start a transaction, select the contents of the row and compare it with what you are about to update. If they do not match, upgrade and complete the transaction. If they match, the transaction is rolled back.

Answer 3

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

Answer 4

This is when the DAL is convenient. It keeps track of all columns, so if none has changed, I don’t even send the UPDATE statement to the database.

Answer 5

It looks like you are going through a table and changing some rows, then you want to go back to the table again through the table and update the timestamp for the rows just changed.

Do not do this in two passes. Just update the date and time while updating any other columns that you change:

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

Or do you post update instructions for all rows, but for most rows, it just sets the value that it already has? Do not do this. Exclude those lines that will not be changed in your where clause:

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

Answer 6

It depends on whether you are managing the data or not. Seb above is true, saying that you should check the old data against the new data before performing the update. But what if the data is not under your control?

Say you are a web service that is prompted to upgrade. Then the only way to check is to query existing data and compare it with the new data.

I don’t know of any SQL functionality that would detect if the update really changed any data or not.

In SQL, there are ways to determine how many rows were included in an update statement. I don’t know how to determine if the update operator actually changed any data, which would be interesting to know.

Answer 7

If you are using sql 2005/2008, you can do the following in a stored procedure.

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

You can then select from @tempTable to check if prevReadKey and readKey have a similar value, if both have a similar value, you can reset your last modified datetime.

Thus, you do not need to run multiple queries in the table in the case when the value really changes. But yes, in the case when the value does not change, this will lead to the dismissal of two update statements, where there is no need. This should be good if these cases are rare.

PS NOTE. - The specified request may be syntactically incorrect because it has not been verified. But this is exactly how your problem can be solved. I did this as follows, using the OUTPUT clause with the Merge statement in one of my projects, and this can also be done using the update statement. Here is the link in the OUTPUT section

Answer 8

You CAN write an INSTEAD OF UPDATE trigger in T-SQL, where you could do what was suggested above in the DAL layer — compare the values ​​in the existing record and the values ​​in the update statement and either apply the update or not. You can use the Columns_Updated () function in a trigger to see if something is updated and accordingly.

It is not particularly efficient from the point of view of the computer, but you can write it once and it will handle this situation no matter which application, stored procedure or other process tried to update the record.