MySQL IF NOT EXISTS

In MySQL, how do I do an insert based upon whether or not a row already exists in a table? The SQL below doesn't seem to work, so I'm looking for another way to accomplish this. Below is all of the code that I have so far (I'm in the process of converting this from MS SQL). I'm sure there are several things wrong with it, but I am specifically looking for the if not exists syntax. If anyone would like to take a shot at making the whole thing work, then I won't complain. :)

CREATE PROCEDURE INTRA_UpdateEmployee (
in_Action varchar(10),
in_ID int,
in_FirstName varchar(255),
in_LastName varchar(50),
in_Company int,
in_Department int,
in_Title varchar(255),
in_Supervisor varchar(1),
in_EmailAddress varchar(255),
in_OfficePhone varchar(15),
in_HomePhone varchar(15),
in_CellPhone varchar(15),
in_FaxNumber varchar(15),
in_IMName varchar(50)
)

sp:BEGIN

IF in_Action = 'insert'
      IF NOT EXISTS (Select ID From employees Where ID = in_ID)
      THEN
            Insert Into employees (ID, Name, Password, InActive, Extension)
            Values (in_Action, in_ID, in_FirstName, in_LastName, in_Company, in_Department, in_Title, in_Supervisor, in_EmailAddress, in_OfficePhone, in_HomePhone, in_CellPhone, in_FaxNumber, in_IMName);

            Select 'Insert Successful.' as Message;
            leave sp;
      ELSE
            Select 'There is already a user with this name.' as Message;
            leave sp;
      END IF
END
ELSEIF in_Action = 'update'

      IF EXISTS (Select ID From Users Where ID = in_ID)

            Update employees
            Set firstname = in_FirstName,
                  lastname = in_LastName,
                  company = in_Company,
                  department in_Department,
                  title = in_Title,
                  supervisor = in_Supervisor,
                  emailaddress = in_EmailAddress,
                  officephone = in_OfficePhone,
                  homephone = in_HomePhone,
                  cellphone = in_CellPhone,
                  faxnumber = in_FaxNumber,
                  imname = in_IMName                  
            Where ID = in_ID;

            Select 'Update Successful.' as Message;
            leave sp;
      END IF

ELSE
      Select 'Invalid Action.' as Message
      leave sp;
END IF

END
lukeinjaxAsked:
Who is Participating?
 
todd_farmerConnect With a Mentor Commented:
Maybe more like this:

IF in_Action = 'insert'
     SET @existCount = 0;
     SELECT @existCount := COUNT(*) FROM employees Where ID = in_ID;
     IF @existCount > 0;
      THEN
            Insert Into employees (ID, Name, Password, InActive, Extension)
            Values (in_Action, in_ID, in_FirstName, in_LastName, in_Company, in_Department, in_Title, in_Supervisor, in_EmailAddress, in_OfficePhone, in_HomePhone, in_CellPhone, in_FaxNumber, in_IMName);

            Select 'Insert Successful.' as Message;
            leave sp;
      ELSE
            Select 'There is already a user with this name.' as Message;
            leave sp;
      END IF
0
 
todd_farmerCommented:
Does the table have a primary or unique key (it appears not)?  If it does, you can use INSERT IGNORE ....

If it does not, create a UNIQUE constraint on the combination of columns that need to be identical, then use INSERT IGNORE.
0
 
lukeinjaxAuthor Commented:
I would like to have a message come back from the stored procedure that tells whether or not the row was updated. I have a primary key of ID on the table, but it is auto-incremented, so it would not create a violation of the key constraint by inserting into the table since I am not specifying that column in the insert statement. I could create a unique key based upon the columns that need to be unique, but it seems that INSERT IGNORE would simply suppress insert errors, not tell me that the row already exists so I can give the user a chance to change and resubmit on the front end. Am I missing something?
0
Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

 
lukeinjaxAuthor Commented:
For some reason, I'm having problems with what you suggested. I'm sure it's something minor, but I can't figure it out. Here is my code as it is now...

CREATE PROCEDURE INTRA_UpdateEmployee (
in_Action varchar(10),
in_ID int,
in_FirstName varchar(255),
in_LastName varchar(50),
in_Company int,
in_Department int,
in_Title varchar(255),
in_Supervisor varchar(1),
in_EmailAddress varchar(255),
in_OfficePhone varchar(15),
in_HomePhone varchar(15),
in_CellPhone varchar(15),
in_FaxNumber varchar(15),
in_IMName varchar(50)
)

sp:BEGIN

      DECLARE @existCount int;

      IF in_Action = 'insert'
    SET @existCount = 0;
        Select @existCount := Count(*) From employees Where ID = in_ID;

            IF @existCount > 0

                  Insert Into employees (ID, Name, Password, InActive, Extension)
                  Values (in_Action, in_ID, in_FirstName, in_LastName, in_Company, in_Department, in_Title, in_Supervisor, in_EmailAddress, in_OfficePhone, in_HomePhone, in_CellPhone, in_FaxNumber, in_IMName);

                  Select 'Insert Successful.' as Message;
                  leave sp;
            ELSE
                  Select 'There is already an employee with this name.' as Message;
                  leave sp;
            END IF
      END
      ELSEIF (in_Action = 'update')
        SET @existCount = 0;
        Select @existCount := Count(*) From employees Where ID = in_ID;

            IF @existCount = 0

                  Update employees
                  Set firstname = in_FirstName,
                        lastname = in_LastName,
                        company = in_Company,
                        department in_Department,
                        title = in_Title,
                        supervisor = in_Supervisor,
                        emailaddress = in_EmailAddress,
                        officephone = in_OfficePhone,
                        homephone = in_HomePhone,
                        cellphone = in_CellPhone,
                        faxnumber = in_FaxNumber,
                        imname = in_IMName
                  Where ID = in_ID;

                  Select 'Update Successful.' as Message;
                  leave sp;
            END IF

      ELSE
            Select 'Invalid Action.' as Message
            leave sp;
      END IF

END

I'm getting:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '@existCount int;

    IF in_Action = 'insert'
    SET @existCount = 0;
      Selec' at line 20
0
 
LowfatspreadCommented:
which Version of Mysql are we dealing with?

MySql is very dependant on versions as to the scope of the sql syntax that can be utilised....

in general you should attempt to change the processing to

insert into mytable
    (column list)
   select @col1,@col2 , .... values
       from AsingleRowTable
      Where @id not in (select id from mytable)

Update mytable
   set col1=@col1,Col2=@col2
  where id=@id



 
0
 
lukeinjaxAuthor Commented:
Lowfatspread, which part of the code are you referring to? Are you saying that I shouldn't use the input variables in my insert statements?
0
 
lukeinjaxAuthor Commented:
Finally got it working with the following...

CREATE PROCEDURE `INTRA_UpdateEmployee`(
in_Action varchar(10),
in_ID int,
in_FirstName varchar(255),
in_LastName varchar(50),
in_Company int,
in_Department int,
in_Title varchar(255),
in_Supervisor varchar(1),
in_EmailAddress varchar(255),
in_OfficePhone varchar(15),
in_HomePhone varchar(15),
in_CellPhone varchar(15),
in_FaxNumber varchar(15),
in_IMName varchar(50)
)
sp:BEGIN

      DECLARE existCount int;

  IF (in_Action = 'insert') THEN
        SET existCount = 0;
        Select existCount = Count(*) From employees Where ID = in_ID;

            IF (existCount > 0) THEN
                  Insert Into employees (ID, firstname, lastname, company, department, title, supervisor, emailaddress, officephone, homephone, cellphone, faxnumber, imname)
                  Values (in_Action, in_ID, in_FirstName, in_LastName, in_Company, in_Department, in_Title, in_Supervisor, in_EmailAddress, in_OfficePhone, in_HomePhone, in_CellPhone, in_FaxNumber, in_IMName);

                  Select 'Insert Successful.' as Message;
                  leave sp;
            ELSE
                  Select 'There is already an employee with this name.' as Message;
                  leave sp;
            END IF;
      ELSEIF (in_Action = 'update') THEN
        SET existCount = 0;
        Select existCount = Count(*) From employees Where ID = in_ID;

            IF (existCount <> 0)
        THEN

                  Update employees
                  Set firstname = in_FirstName,
                        lastname = in_LastName,
                        company = in_Company,
                        department = in_Department,
                        title = in_Title,
                        supervisor = in_Supervisor,
                        emailaddress = in_EmailAddress,
                        officephone = in_OfficePhone,
                        homephone = in_HomePhone,
                        cellphone = in_CellPhone,
                        faxnumber = in_FaxNumber,
                        imname = in_IMName
                  Where ID = in_ID;

                  Select 'Update Successful.' as Message;
                  leave sp;
            END IF;

      ELSE
            Select 'Invalid Action.' as Message;
            leave sp;
      END IF;

END
0
All Courses

From novice to tech pro — start learning today.