Creating procedure that add value when it doesn't exist (Syntax error)-CodePudding

I'm trying to create a procedure that add a value that doesn't exist but I'm currently getting a syntax error. I don't know how to correct the error. Any help will be appreciated!

Error Code: 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 'WHERE NOT EXISTS (SELECT * FROM Animals WHERE Name = animalName); END' at line 5


Delimiter //
CREATE PROCEDURE AddUniqueName (In animalName varchar(100) )
BEGIN
  INSERT INTO Animals (Name)
  SELECT animalName
  WHERE NOT EXISTS (SELECT * FROM Animals WHERE Name = animalName);
END //
    
Delimiter ;

CodePudding user response：

Use INSERT IGNORE statement for uniq keys.

let's assume you have a table

create table Animals( name varchar(255) unique key);

then

Delimiter //
CREATE PROCEDURE AddUniqueName (In animalName varchar(100) )
BEGIN
    INSERT IGNORE INTO Animals (Name) values(animalName);
END //
    
Delimiter ;