MySQL Bugs: #22813: replace: change the internal behaviour from delete+insert to update

Bug #22813	replace: change the internal behaviour from delete+insert to update
Submitted:	29 Sep 2006 7:05	Modified:	29 Sep 2006 16:37
Reporter:	Maciej Pilichowski	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	MySQL Server	Severity:	S4 (Feature request)
Version:	5.0.24a	OS:	Linux (opensuse 10.0)
Assigned to:		CPU Architecture:	Any

Description:
I only consider the situation when the replaced data already exist. When replace means just insert everything is ok.

I think that it is the last call to redesign the behaviour of replace, when the triggers are relatively fresh. Why? Well currently replace physically means delete+insert but logically it is as a matter of fact update. Furthermore it is the solution to annoying and errorprone problem with "check is exists and then update or insert" (at least it was my everyday problem with MSSQL Server 2K), however it is not really equivalent to it.
Now the problem may seem not so important but with triggers it is -- notice that replace in fact fires the on-delete trigger. With well designed architecture of user database it can be a problem because nobody actually deletes anything -- it is only a "_replace_ment".

So, one advantage would be consistency ("do what you mean"), another -- smooth integration with triggers. On new data insert-trigger is fired, on replacement only _one_ trigger is fired -- on-update, not on-delete & on-insert.

However there is also another advantage of such change -- richer capabilities of the syntax. Currently when you omit some columns for the rest the default values are set (there is no other way since it is equivalent to insert). But with change to update only the affected columns can be changed and other could be unaffected (or set to default -- depending how syntax is built and how you use it).

For me personally the most important thing is trigger -- it is a serious flaw to see on adding data that deletion trigger kicks in.

How to repeat:
This a wish, not a bug.

Suggested fix:
This a wish, not a bug.

please see also bug #13479 , the situation was discussed there.

Thanks for the link, the report you mentioned is started about something different however final conclusion is not valid for 5.0.24a. In this version server works as described in documentation and replace = delete + insert.

I do not think this feature may be implemented in 5.0.x. We have a documented, intended and consistent behaviour (even if not the best possible one) there, and no real reasons to change it "back" in the next version again.

on reflection, I think that the described feature  is what "insert on duplicate key update" does. Therefore the current implementation covers for both scenarios.

replace = insert OR delete and insert
insert on dup... = insert OR update

Thank you for the answer, mmm, it is a pity -- changing/adding data should not involve deleting (triggers problem -- I am curious if the design of current replace behaviour is dated before or after trigger feature).

However, I hope I'll see it someday, not necessarily in 5.0.x line, maybe in 5.1.x? Would be great.

Martin, thank you for the tip, in that case is possible to make a workaround and avoid deleting. Now I check the triggers to be sure :-)