Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

MySQL IF NOT EXISTS

Posted on 2007-03-19
7
Medium Priority
?
24,915 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 150 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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 

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

Fill in the form and get your FREE NFR key NOW!

Veeam® is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

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

Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
In this blog, we’ll look at how improvements to Percona XtraDB Cluster improved IST performance.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

730 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