Re: INSERT INTO if UPDATE SET fails (MySQL)

On Thu, 15 Oct 1998 22:28:12 +0200, Tor Houghton <torh_at_nextel.no> wrote:

>I couldn't find an general SQL group, so I will try here. Shoot me if
>I'm babbling nonsense.
>
>I've got the following problem. I need to do
>
> UPDATE table SET counter=counter+A WHERE datestamp=NOW() AND
> constraint=B;
>
>If this meets the set criteria, this is no problem. But what I want
>is to create the table record if it doesn't exist. I thought I might
>use IF NOT EXISTS, but can't seem to get it to work.

In SQL, there is no single statement that will either update a record (if one matching the criteria exists) *or* insert one if it does not. You would need to use two statements to do this: UPDATE and INSERT.

You should be able to execute both for a given set of data. Wording of the WHERE clause criteria in each would ensure that only one of the actions would take place, the update or the edit.

Alternately, you could use a stored procedure. In the stored procedure check for the existence of one or more rows matching the criteria and, if they exist, perform the UPDATE statement. If they do not exist, perform the INSERT.

//////////////////////////////////////////////////////////////////////////
Steve Koterski                      "The knowledge of the world is only to
Technical Publications              be acquired in the world, and not in a
INPRISE Corporation                 closet."
http://www.inprise.com/delphi          -- Earl of Chesterfield (1694-1773)