Error using Delimiters in mySql

Question

i am trying to run this procedure.

USE dbsample
DELIMITER //
CREATE PROCEDURE updateBooks (IN book_id VARCHAR(20), IN units INT)
BEGIN
UPDATE books
SET quantity = quantity – units
WHERE isbn = book_id;
END //
DELIMITER ;

though i have checked at various places an syntax seems right to me. BUT i am getting the following error

Error code 1064, SQL state 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 'DELIMITER // CREATE PROCEDURE updateBooks (IN book_id VARCHAR(20), IN units INT)' at line 2 Line 1, column 1

Error code 1064, SQL state 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 'END // DELIMITER' at line 1 Line 8, column 1

Any help would be great. ANd i am using mySQL version 5.6

peterm peterm · Accepted Answer · 2013-11-29T04:34:46

You have to terminate USE statement with a delimiter (in your case it is a semicolon).

USE dbsample;
            ^

Another problem is that you're using dash (–) instead of minus character (-) in

... SET quantity = quantity – units
                            ^

UPDATE: Now since UPDATE is the only statement in your procedure you don't have to use BEGIN ... END block and change DELIMITER

CREATE PROCEDURE updateBooks(IN book_id VARCHAR(20), IN units INT)
UPDATE books
   SET quantity = quantity - units
 WHERE isbn = book_id;

Here is SQLFiddle demo

Error using Delimiters in mySql

1 Answers