Re: REPLACE INTO table a la mySQL

Dale Johnson wrote:
> I know we're not in the business of copying mySQL,
> but the REPLACE INTO table (...) values (...) could be
> a useful semantic. This is a combination INSERT or
> UPDATE statement. For one thing, it is atomic, and
> easier to work with at the application level. Also
> if the application doesn't care about previous values,
> then execution has fewer locking issues and race
> conditions.
>
> comments?

First it's not standard SQL, so chances aren't that good.
Second, how do you think the system should behave in the
following case:

* Table A has one trigger BEFORE INSERT doing some checks
plus inserting a row into table newA and updating a row in
table balanceA. It also has triggers BEFORE UPDATE and
BEFORE DELETE that update balanceA.

* Now we do your REPLACE INTO

The problem is that in a concurrent multiuser environment you
cannot know if that row exists until you actually do the
insert (except you lock the entire table and check for).
Since there's a BEFORE trigger which potentially could
suppress the INSERT, you can't do the insert before fireing
it. Now it has been run, did it's inserts and updates and the
statement must be converted into an UPDATE because the row
exists - how do you undo the trigger work?

I know, mySQL doesn't have triggers, referential integrity
and all that damned complicated stuff. That's why it can have
such a powerful non-standard command like REPLACE INTO.

Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck(at)Yahoo(dot)com #

_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com