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
arow 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
actuallydo 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'sinserts and updates and the statement must be converted into an UPDATE because the row exists - how do you
undothe trigger work?
I know, mySQL doesn't have triggers, referential integrity and all that damned complicated stuff. That's why it
canhave 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@Yahoo.com #
_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com
