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
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