Solved

MySQL IF NOT EXISTS

Posted on 2007-03-19
7
24,852 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 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
CHALLENGE LAB: Troubleshooting Connectivity Issues

Goal: Fix the connectivity issue in the lab's AWS environment so that you can SSH into the provided EC2 instance.  

 

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

[Webinar] Learn How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

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…
This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

626 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