Oracle: Delete and Re-Insert a Row in the Same Statement
It’s probably worth some explanation to understand where I want to drive you: when you run an update in Oracle, the changes are made at the point that is consistent during the whole execution of the update. This allows you to run a command like the one below, even if ID is the primary key of T:
update t
set id=(case id
when 1 then 2
when 2 then 1
end)
where id in (1,2);
If that was not the case, you would have to DEFER the constraint validation to the commit time every time you ran a command like the one above, or you would get an error like:
ORA-00001: unique constraint T_PK violated
To read more about how updates work in Oracle, look at Tom Kyte’s Write Consistency Part I, Part II, Seeing a Restart, and Part III, Why is a Restart Important to Us. But that’s a tangent to the subject of this post. What I’ll show here is that you can run a DELETE and an INSERT as parts of the same statement.
