Update values in the database directly or delete - add them again
Whenever we need to update the database; first we remove the values from the table and then we add the last values. This will ensure that everything is updated correctly.
This adds a bit of system overhead, but we had no performance issues because of this.
Is it always the best?
source to share
Not.
Use the UPDATE statement.
Also, if you're worried about integrity, include it in your transaction:
BEGIN TRAN T1
-- This update is part of T1
UPDATE Table1 SET Col1='New Value' WHERE Col2 = @Id;
-- Time to commit your changes.
-- If for any reason something fails,
-- everything gets rolled back
COMMIT TRAN T1
source to share
By "database" I am assuming you mean a SQL database. The danger of doing delete
, then insert
is that if you are not careful with transactions, it is non-atomic. In the time between deleting a row and inserting a new one, someone may have missed a similar row, causing an error insert
under some constraint. It is also a waste of effort, because there update
, as J. Holland points out. Everything will be done in one shot. Putting it yourself in your own transaction is probably not necessary as updates are atomic.
If you do delete-then-insert because you are not sure if the row already exists, some databases have a statement replace
that will insert if there is no row or update if there is.
source to share
Like everyone else, use UPDATE.
One additional reason: if you have a CASCADE DELETE under any referential integrity constraints, DELETE, then the INSERT loses subordinate data. If you don't have a CASCADE DELETE, you can choose not to delete the record because it still references other tables.
source to share
Delete-insert is a higher cost operation than update.
If the reason is not obvious, it is because you need to preserve undo / redo information for the entire deleted row, including changing all index entries and checking for referential integrity violations caused by the deletion, and then insert a new row with all undo / redo, indexes , integrity checks, etc. the newline could also be in another block to be deleted, so now you're doubling (at least) the physical I / O requirements.
Also consider the impact on triggers and logging. And you have to grant insert and delete privileges if you can just grant update privileges.
I'm sure there is a regular programming paradigm such as "When I want to assign a new value to a variable, I always set it to zero first ...", but I'm afraid it won't convey the complete horror of the situation.
source to share