Re: SQL update/insert statement.

From: Sharkie <sharkie2_at_my-deja.com>
Date: Mon, 27 Mar 2000 18:54:55 GMT
Message-ID: <8boapm$ja4$1@nnrp1.deja.com>

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

--
If the human brain were so simple
that we could understand it,
we would be so simple we couldn't.
-Makes Sense... don't it?

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Mon Mar 27 2000 - 12:54:55 CST