update vs. delete - insert

[BEFOR]

While I've let users do updates on their records, I'm starting to wonder why I've heard, here and there to the effect "never let a user do an update...always do a delete followed by an insert"

What's the reasoning behind this?

[laserlight]

I've never heard of that maxim.

It would be pretty problematic for data that frequently changes and uses an autoincrement key, I think.

[Roger_Ramjet]

Old-fashioned notion based upon the problems inherent in concurrent updates in a client-server environment over slow links (like the web). In that situation it is all too easy for concurrent updates to clash with the last one winning. ie second update is done on the old edition of the record, overwritting the first user's update without them being aware of it as they have already moved on. By deleting the old edition first, the second update fails and the second user is presented with the new edition instead. On a busy database, some unlucky users could spend all day trying to get their update done.

[nemonoman]

Actually there's a much simpler reason: DELETE/INSERT is always guaranteed to work. You don't need to code a test to check whether you need to update or insert. You just DELETE -- if the id doesn't exist, there's no harm done -- then INSERT. It's the quick and dirty method.

[Lars_Berg]

And how do you handle foreign keys using that strategy?

[Roger_Ramjet]

Originally posted by nemonoman
Actually there's a much simpler reason: DELETE/INSERT is always guaranteed to work. You don't need to code a test to check whether you need to update or insert. You just DELETE -- if the id doesn't exist, there's no harm done -- then INSERT. It's the quick and dirty method.

Well, yes if the question was about INSERT, but it was not. (even if it were then this would still be out of date as REPLACE or ON DUPLICATE KEY UPDATE handle this).

The question was, I believe, about whether to handle user's UPDATEs of existing records that way as well. It is not just a quick-and-dirty fix for webnerds who don't know how to do client-server databases properly. It is for instance a very valid strategy for eg an airline booking system where many people could be viewing available seats and trying to book the same ones at the same time. And of course it creates its own set of problems as Lars points out.

I am not saying that I recomend it, just that at one time it was a regularly used strategy when all remote links were extremely slow, think 2Kb WAN links in the 70s or 14Kb dail-up in the 80s.

Personally, I have always used other forms of transaction queuing and integrity checking. Ah! So that's what the timestamp column type is for 😃