List:General Discussion« Previous MessageNext Message »
From:Alec.Cawley Date:July 26 2004 3:52pm
Subject:Re: INSERT if record NOT EXISTS
View as plain text  
Adaikalavan Ramasamy <ramasamy@stripped> wrote on 26/07/2004 
16:31:44:

> But I have another related question. How do I write an IF ELSE command
> with MYSQL. In this context, I want it to return myID if the record
> already exists, otherwise insert into database.
> 
> This naive syntax does not work :
> IF EXISTS (SELECT myID FROM tb WHERE firstname='Jack' AND
> lastname='Doe') ELSE (INSERT INTO tb(firstname, lastname) VALUES
> ('Jack', 'Doe');

I don't think it is possible. There is a fairly serious architectural 
split in SQL between SELECT, which is read-only and returns a two 
dimensional table of results, and UPDATE (and its twins REPLACE and 
INSERT), which update the database and return a single integer, usually 
the count of rows updated. The command you are trying to do combines both. 
The transport level would therefore not know whether to carry a 
SELECT-style reply or an UPDATE-style reply.

This is a limitation on SQL, rather than MySQL. You have to do two 
operations.

        Alec



Thread
INSERT if record NOT EXISTSAdaikalavan Ramasamy26 Jul
  • Re: INSERT if record NOT EXISTSAlec.Cawley26 Jul
  • Re: INSERT if record NOT EXISTSgerald_clark26 Jul
  • Re: INSERT if record NOT EXISTSMichael Dykman26 Jul
    • Re: INSERT if record NOT EXISTSAdaikalavan Ramasamy26 Jul
      • Re: INSERT if record NOT EXISTSMarc Slemko26 Jul
  • Re: INSERT if record NOT EXISTSHarald Fuchs27 Jul
Re: INSERT if record NOT EXISTSAdaikalavan Ramasamy26 Jul
  • Re: INSERT if record NOT EXISTSAlec.Cawley26 Jul
  • Re: INSERT if record NOT EXISTSKeith Ivey26 Jul
    • Re: INSERT if record NOT EXISTSAdaikalavan Ramasamy26 Jul