So today a professor told us that when the database has to make an update, internally (at low level) it makes a delete and then an insert with the updated fields. He then said that this is something made across all databases and then I started a discussion telling that I thought that it had no sense but I didn't had enough resources to support my position. He seems to know a lot but I can't understand why would dbs do that.
I mean, I know that if you update a field and you need more space for that row, then it may delete the row physically and put it at the end with the new data. But if for example you reduce the space used, why would it delete and re insert it at the end?
Is this even true? What are the benefits?
Best Answer
No, it is an implementation detail. A database may implement a suitable update in place if it so chooses.
Splitting an update into a delete followed by an insert generally makes the implementation simpler. Potential side benefits include the ability to avoid transient key violations in a unique index, by suitably sorting the split delete/insert operations.
A split update may be a little slower and generate more log than a true in-place update (which is not always possible anyway).
As Kin noted in a comment, if you need an example (for SQL Server) see:
It is also related to implementing MVCC. In Wikipedia page about MVCC, it is mentioned:
See also page 60 of PostgreSQL Internals pdf by Bruce Momjian (Postgres uses MVCC): "
UPDATE
is effectively aDELETE
and anINSERT
."