1
CREATE PROCEDURE update_table(
    IN choice INT(4),
    IN id VARCHAR(50),
    IN string VARCHAR(50)
)
BEGIN
UPDATE salesman
set salesman_name = IF(choice = 1, string, salesman_name)
where salesman_id = id
UPDATE salesman
set date = IF(choice = 2, string, date)
where salesman_id = id
END

if choiceis 1, change salesman_name as string

if choice is 2, change date as string

can you explain me what i'm doing wrong?

it works fine with a single update, my guess is there is another way to implement if but i couldn't.

if choice = 1 then
update salesman set salesman_name = string where salesman_id = id

...i tried this version too but still, not working.

DELIMITER //
CREATE PROCEDURE update_table(
    IN choice INT(4),
    IN id VARCHAR(50),
    IN string VARCHAR(50)
)
BEGIN
UPDATE salesman set salesman_name = IF(choice = 1, string, salesman_name) where salesman_id = id;
UPDATE salesman set date = IF(choice = 2, string, date) where salesman_id = id;
END //
DELIMITER ;

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' at line 1

also says this:

ERROR: Unknown Punctuation String @ 11 (last line)

  • Are you using the procedure editor interface in phpMyAdmin to do this? What have you selected in the text field for your delimiter? – Isaac Bennetch May 24 '16 at 01:15

1 Answers1

0

When a stored procedure has more than one statement, they need to be terminated with ;

To do that, you need to tempoararily change the delimiter so you can end the procedure. Here's a SO answer with an example of how to do that: MySQL create stored procedure syntax with delimiter

Community
  • 1
  • 1
bitfiddler
  • 2,095
  • 1
  • 12
  • 11
  • He doesn't appear to be talking about a 1065 syntax error. Is he? Then again maybe he is. The whole thing is too vague. – Drew Apr 30 '16 at 16:42
  • yes i am. it says syntax error where second update starts (line 10) i've tried it with a delimiter too. now the syntax error is for `DELIMITER $$` –  Apr 30 '16 at 17:13
  • just found out the procedure is actually working when called with `CALL`... the error is for the last line only(where delimiter is set to ";" again) –  Apr 30 '16 at 17:41