Re: SQL update/insert statement.

From: Connor McDonald <connor_mcdonald_at_yahoo.com>
Date: Tue, 28 Mar 2000 19:51:49 +0800
Message-ID: <38E09CD5.4E4D@yahoo.com>

Thomas Kyte wrote:
>
> A copy of this was sent to Sharkie <sharkie2_at_my-deja.com>
> (if that email address didn't require changing)
> On Mon, 27 Mar 2000 18:54:55 GMT, you wrote:
>
> >In article <8bo31k$aae$1_at_nnrp1.deja.com>,
> >Sean <dolans_at_my-deja.com> wrote:
> >> I have a table that I want to either update the counter on or else
> >> insert a new record if it doesn't exist.
> >>
> >> In PL/SQL I can do a count(id) on the table and update it if > 0 or
> >> insert if not there ( equals 0 )
> >>
> >> Is there a more efficient way of doing this in an SQL Statement?
> >
> >Yes, define a cursor and use FOUND, ex:
> >
> >declare
> >
> > cursor my_cur is
> > select some_columns
> > from my_table
> > where some_clause
> > for update;
> > my_rec my_cur%rowtype;
> >
> >begin
> > open my_cur;
> > fetch my_cur into my_rec;
> > if (my_cur%found) then
> > update my_table
> > set your_columns=your_values
> > where current of my_cur;
> > else
> > insert into my_table your_columns
> > values (your_values);
> > close my_cur;
> > commit;
> >end;
> >
> >hih
>
> Or the shorter way (requires a unique constraint but its by ID as above so there
> should be one):
>
> begin
> insert into My_Table ( your columns ) values ( your values );
> exception
> when dup_val_on_index then
> update my_table set ( your columns ) = ( your values )
> where your where clause;
> end;
>
> The first one that reads the record and then does either an update or insert is
> prone to fail in a multi-user environment as 2 people running this close
> together would both find "no record" and both try to insert -- one of them will
> fail (hard to find sind the logic 'looks ok')
>
> --
> http://osi.oracle.com/~tkyte/
>
> Thomas Kyte tkyte_at_us.oracle.com
> Oracle Service Industries Reston, VA USA
>
> Opinions are mine and do not necessarily reflect those of Oracle Corporation

or

begin
  update table
  set x = y
  where ...

  if sql%rowcount = 0 then
    insert into ...
  end if;
end;

Ideally one would want to use the 'update first' or 'insert first' code dependant on which you expect to be higher frequency.

--

---

Connor McDonald
http://www.oracledba.co.uk

We are born naked, wet and hungry...then things get worse Received on Tue Mar 28 2000 - 05:51:49 CST