Re: Insert Or update

On Saturday 24 April 2004 00:09, you wrote:
> And in the proper way to do this in a relational database, those rows
> are locked by the application until the user presses the OK button.

This kind of change is very rare and is usually done by "admin" user. There's
no need to lock those rows between load and update.

> As an addendum to Bruno's comment (which is true), I'll add that the
> "insert or update" command (in MySQL it's a "replace" command) really
> suggests a broken architecture in your database.  It's okay, we often
> use relational databases for non-relational tasks simply because we have
> nothing else available, but don't be surprised when it breaks.

In my case, insert-or-update is just a handy shortcut for a longer operation.
It appears that this shortcut can be useful to many people in various
situations, as the requests for it are constantly appearing from time to
time.

mysql's "replace" is broken in a sence that for existing row, it's actually
DELETE+INSERT, and this breaks lots of things. For example, it'll assign new
ID for auto_increment column type. This is a big problem for foreign keys
(but not a problem for mysql due to obvious reasons:).

insert-or-update is useful in a general case when you need to merge some new
set of data (maybe coming as an update from some external source) with an
existing set of data in some table. Is it a relational or a non-relational
task ?

Are there any plans on adding this feature per sql 2003 spec ?

--
Best regards,
Igor Shevchenko