CODEWITHSUNDEEP
mysqlperlinsertnot-exists
roger
roger

Reputation: 35

MySQL Insert where not exists does not work

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?

Upvotes: 0

Views: 423

Answers (1)

ethrbunny
ethrbunny

Reputation: 10469

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.

Upvotes: 1

Related Questions