Re: SQL update/insert statement.

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;

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 Received on Mon Mar 27 2000 - 15:23:26 CST