Re: INSERT INTO if UPDATE SET fails (MySQL)

From: john frame <jbframe_at_hotmail.com>
Date: 1998/10/16
Message-ID: <ouAV1.2153$Mb7.1297573@news.rdc1.bc.wave.home.com>#1/1

SQL has two separate commands:
update and insert.

You need to have a function that tries to update, or upon failure does the insert. This can be done in SQL if your database lets you write your own database functions (oracle does).

funcion my_update(parameters) return VARCHAR2 is BEGIN
  update the_table
  set something = aparameter
  where somethingelse = another_parameter

  IF sql%rowcount = 0 then
   insert into the_table (columns)
   values (parameters)
  END IF;   RETURN ('SUCCESS');
EXCEPTION
   WHEN OTHERS THEN
      RETURN (sqlerrm) ;
END; then you can use any sql such as:
select my_update(parameters)
from dual;

and if this returns 'SUCCESS' then all is good.

Basically you need middleware (either in your application, on the database, or wherever it lives) SQL itself, does not support it. Received on Fri Oct 16 1998 - 00:00:00 CDT