0

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.

Veksi
  • 2,868
  • 1
  • 20
  • 52
  • @Shadow It's not about syntax as such, maybe more about why it doesn't work uniformly. See the added edit. – Veksi Oct 11 '16 at 18:20
  • Check out the versions of the various installations as a starting point. Release notes then may give you an answer why the syntax works in your version. Although it is strange to have a select without a from, but with a where criteria present. I would say it should not work in any MySQL installations. Is not it possible that in your environment the error messages are not returned, only warnings? – Shadow Oct 11 '16 at 18:29

1 Answers1

0

I believe it's because of missing FROM from the exists query. However, I guess the query is checking if the record already exists in the table, and if not, inserts it. Below is another example of to do it:

CREATE TABLE Test (Id INT);

DELIMITER //

CREATE PROCEDURE MyProc (IN _Id INT)
BEGIN
   DECLARE total INT;
   select count(*) into total
   from Test where id = _Id;
   if(total = 0) then
     insert into Test(Id) values(_id);
   end if;
END//

DELIMITER ;
Darshan Mehta
  • 27,835
  • 7
  • 53
  • 81