Solved

MySQL IF NOT EXISTS

Posted on 2007-03-19
7
24,725 Views
Last Modified: 2007-12-19
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
0
Comment
Question by:lukeinjax
  • 4
  • 2
7 Comments
 
LVL 30

Expert Comment

by:todd_farmer
ID: 18748451
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
 

Author Comment

by:lukeinjax
ID: 18748534
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
 
LVL 30

Accepted Solution

by:
todd_farmer earned 50 total points
ID: 18748637
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:lukeinjax
ID: 18749394
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
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 18750889
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
 

Author Comment

by:lukeinjax
ID: 18751313
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
 

Author Comment

by:lukeinjax
ID: 18751796
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

Featured Post

How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Creating and Managing Databases with phpMyAdmin in cPanel.
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

679 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question