These are the options I've thought of:

DotNet Dave:

UPDATE mytable set f1=newval where id=666 and f1 := f1ValueFromSelect

Charlie Checksum:

UPDATE mytable set f1=newval where id=666 and md5(concat(id,f1)) := checksumFromSelect

Timothy Timestamp:

UPDATE mytable set f1=newval where id=666 and mytimestampfield := timestampFromSelect

Veronica Version:

UPDATE mytable set f1=newval, version=version+1 where id=666 and version=versionFromSelect

I like Dave, as no schema changes required. Have to keep all of record's original data on client though. How to compare blobs?

Charlie is good in that less data to send to client. Not sure about performance with blobs/clobs

Tim and Veronica are good as pretty simple though may require schema change. Timestamp looks a bit more foolproof. How cross-platform is it?


  • You should tag questions like this with your platform - in this case the options available for this vary with the actual DBMS in use. Apr 18, 2012 at 8:17
  • well, they can all do 1, 3 and 4, and a few can do 2. i am looking for a general solution. Apr 18, 2012 at 8:40

1 Answer 1


Depends on your DBMS platform to some extent. On the SQL Server based development I did back in the late Jurassic period we used timestamps as these are automatically updated when the record is written.

You load a record with:

select Foo
  from FooTab
 where FooID = 666

And save with

update FooTab
   set Foo = @Foo
      ,Bar = @Bar
 where FooID = @FooID
   and TS = @TS

select @@rowcount as RowCount 

... or some such, depending on where the concurrency was being handled.

Oracle doesn't have such a timestamp, so the Oracle stuff I did around the same era worked by incrementing a version counter.

My view about checksumming and comparing field values is it is fiddlier and therefore easier to make a mistake with, so it's best only used when you don't have the option of using a timestamp or version on the record. And, yes, without applying any google-fu to the question, I can't think of a way to do it with BLOBs off the top of my head either. You might be able to do the comparison separately with something like:

... and hashbytes ('MD5', BlobField) = @BlobHash

So, as an opinion, use timestamping or versioning if you have the option - it's much simpler. You can do field comparisons or hashing if you don't have the option of changing the database, but it's more fiddly.

  • thanks. decided to go with a 'version' int that is updated by a trigger (mysql). didn't wanna risk timestamp as it only has 1sec resolution AFAIK Apr 18, 2012 at 3:31
  • Timestamps on SQL Server aren't actually timestamps as such - effectively they're just a number that will always change as the record is written. Apr 18, 2012 at 8:16
  • According to mssqltips.com/sqlservertip/1501/…, timestamps will at some point change to the ANSI standard in MSSQL, so this method would stop working at that point. When "at some point" will occur is the question:)
    – riffrazor
    Jan 31, 2016 at 16:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.