CODEWITHSUNDEEP
mysql
user1658296
user1658296

Reputation: 1418

MYSQL syntax delimiter and syntax error

I have tried to fix the syntax errors in the following but I can't see what on earth is wrong here:

DELIMITER =
CREATE TRIGGER trigs BEFORE UPDATE ON autoinc 
FOR EACH ROW BEGIN 
DECLARE num_rows INTEGER; 
SELECT (*) INTO num_rows FROM autoinc;
IF num_rows >=3 THEN 
DELETE FROM autoinc LIMIT 1; 
END IF; 
END=
DELIMITER ;

The errors are:

ERROR 1064 (42000): 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 '*) INTO num_rows FROM autoinc; IF num_rows >' at line 4

 ERROR 1064 (42000): 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 '3 THEN

Can any please help me fix this?

Upvotes: 0

Views: 53

Answers (1)

Hartmut Holzgraefe
Hartmut Holzgraefe

Reputation: 2765

As already mentioned in the comments:

SELECT (*) AS num_rows ...

was probably meant to be 

SELECT COUNT(*) AS num_rows ...

And the 

IF num_rows >=3 THEN

breaks as you defined = as delimiter.

Use a delimiter that doesn't occur in your code, e.g.:

DELIMITER //

With these two changes things should work without syntax errors

Upvotes: 2

Related Questions