I came across a problem I don't seem to be able to solve. I'm fairly sure the following definition of InserProc
used to work, but now when I try to create it (tried with SQL Fiddle) I'm encountered with 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
. If that doesn't work, how could insert the variable first checking it doesn't exist using a similar construct (I know there are other possibilities)?
CREATE TABLE Test(Id INT);
SELECT @var := 1;
INSERT INTO Test(Id) SELECT @var;
DELIMITER $$
CREATE PROCEDURE InserProc(in _Id INT)
BEGIN
INSERT INTO Test(Id)
SELECT _Id
WHERE NOT EXISTS
(
SELECT 1
FROM Test
WHERE Id = _Id
);
END$$
<edit: Edited the short script to include delimiters to make it more it more complete. The plot thickens in that SQL Fiddle doesn't accept that syntactically nor does the MySQL installation of a colleague, but on my machine it works just fine. Very strange!
<conlusion: It appears on version 5.7.11-log this works, but on version 5.6.32-log it does not. What is left to be determined is that if this just a feature of this version and goes away in some future version and perhaps just fix the syntax.