Re: INSERT or UPDATE

From: Spencer <spencerp_at_swbell.net>
Date: Fri, 2 Mar 2001 20:54:10 -0600
Message-ID: <z0Zn6.805$RU5.6188@nnrp1.sbc.net>

"Try Out" <kasse_at_kutte.dk> wrote in message news:lkpp9t08gmgcmkf0lu4olo6li11rtobca4_at_4ax.com...
> I need a command that works like REPLACE does on MySQL (definition
> folows). What is the equevalent on oracle?
>
> -- Troels
>
>
> REPLACE [LOW_PRIORITY | DELAYED]
> [INTO] tbl_name [(col_name,...)]
> VALUES (expression,...)
> or REPLACE [LOW_PRIORITY | DELAYED]
> [INTO] tbl_name [(col_name,...)]
> SELECT ...
> or REPLACE [LOW_PRIORITY | DELAYED]
> [INTO] tbl_name
> SET col_name=expression, col_name=expression,...
>
> REPLACE works exactly like INSERT, except that if an old record in the
> table has the same value as a new record on a unique index, the old
> record is deleted before the new record is inserted
>

this is by no means a complete answer to your question, but only a starting point.

there are a couple of approaches you can take to solving the problem, depending on whether the majority of calls will be performing inserts or updates...

here is a procedure that serves as a starting point. for example, you may want to handle the COMMIT outside of the procedure, and there are a couple of exceptions that could be handled better... but it's a start.

CREATE OR REPLACE
PROCEDURE upsert_mytable
(key1val IN VARCHAR2
,col2val IN VARCHAR2
,col3val IN VARCHAR2
)
IS
  CURSOR lcsr_mytable IS
  SELECT ROWID, mykey, mycol1, mycol2
    FROM mytable
   WHERE mykey1 = key1val
     AND ROWNUM = 1
  FOR UPDATE NOWAIT;
  lrec_mytable lcsr_mytable%ROWTYPE;
BEGIN
  OPEN lcsr_mytable;
  FETCH lcsr_mytable INTO lrec_mytable;
  IF lcsr_mytable%NOTFOUND THEN
    INSERT INTO mytable (mykey1,mycol2,mycol3)     VALUES (key1val,col2val,col3val);
  ELSE
    UPDATE mytable

       SET mycol1 = col1val
         , mycol2 = col2val
     WHERE ROWID = lrec_mytable.ROWID;

  END IF;
  CLOSE lcsr_mytable;
  COMMIT;
EXCEPTION
  WHEN OTHERS THEN
    RAISE;
END; to invoke the procedure as a callable statement... e.g.

BEGIN upsert_mytable('keyval','value2','value for col3'); END;

HTH Received on Fri Mar 02 2001 - 20:54:10 CST