MySQL Insert where not exists does not work

Question

Pasted below is my MySQL Stored Procedure.

CREATE PROCEDURE `newrig`(
  IN x varchar(10),
  IN y varchar (10),
  IN z varchar(5),
  OUT a INT
)
BEGIN

        INSERT INTO rig (Name, Model,Type)
        SELECT * from (SELECT x,y,z) as tmp
        WHERE NOT EXISTS 
        (SELECT * FROM rig where Name=x
        AND Model=y
        AND Type=z);    
        SELECT LAST_INSERT_ID() INTO a;

END

and this is how I am calling it from perl

$hDb->do( "call newrig( 'krish','xx','j',\@a);" );

The outcome is; it does not insert the data into the table. Can somebody help?

The first guess would be that the values already exist in the table. — Gordon Linoff, Oct 19 '14 at 12:41

score 1 · Answer 1 · edited May 23 '17 at 12:27

1

Start with:

INSERT INTO rig (Name, Model,Type) values (x,y,z)
    WHERE NOT EXISTS 
    (SELECT * FROM rig where Name=x
    AND Model=y
    AND Type=z);

Be aware that if the record already exists the value you get for last_insert_id won't make any sense.

You would be better served by putting a unique key on x,y,z and using insert ignore.. though that still won't give you the record #.

Read this and this for more info about making this work.

edited May 23 '17 at 12:27

Community

1
1

answered Oct 19 '14 at 12:40

ethrbunny

10,379
9
69
131

MySQL expects a semicolon followed by an END after "values(x,y,z)". Without the where not exists clause it wouldn't work for me. If the record existed the insert ignore technique would require me to do a check on the last insert id. – roger Oct 19 '14 at 13:54
Also, i wonder if INSERT IGNORE would ignore the insert if my primary key is set to auto-increment and PK is the Unique key of the record. – roger Oct 19 '14 at 14:10

MySQL Insert where not exists does not work

1 Answers1