Does mysql enforce special syntax constraints on sql inside stored procedures? (select into problem)

Question

I am often having a very hard time to create a stored procedure in mysql. Syntax which should really be fine just get not parsed within a stored procedure. Here is a simplified version that still doesn't get parsed. I am not able to turn this code into something that can be parsed.

The update..set clause gives problems

UPDATE
I've simpiflied the code even more. Problem still exists. Error messages seems to be rumble

UPDATE 2
Solved, thanks to Mark Byers. The into clause in a select-statement must be positioned carefully. Note how misleading and bad the mysql error mesage is!

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual

   UPDATE page
   SET
   lft = 1
   where lft > 3 AN' at line 22

The code:

CREATE PROCEDURE `move_page_right`( subject_id SMALLINT UNSIGNED, reference_id SMALLINT UNSIGNED)


BEGIN 
 select 
  p.lft, 
  p.rgt, 
  p.rgt - p.lft,
  p.rgt + 1 
 into 
  @subject_old_lft, 
  @subject_old_rgt, 
  @subject_width, 
  @subject_old_right_sibling_lft 
 from page p 
 where p.page_id = subject_id; 


 select p.rgt + 1 
 from page p 
 into @subject_new_lft 
 where p.page_id = reference_id;

UPDATE page 
SET 
lft = 1 
where lft > 3 AND lft < 3;

END

Answer 1

I don't think that the table alias goes in the UPDATE portion. Try just removing the alias. If your UPDATE statement references multiple tables then you can put the updated table in the FROM clause (or any join) and use ONLY the alias in the UPDATE portion.

I don't know about MySQL, but that's how it works in MS SQL, so it may be ANSI standard.

I'm also not sure what you're trying to accomplish. You're setting the columns to themselves, which isn't going to do anything.

Answer 2

Sorry, again I'm just guessing here, but hopefully I'll get it soon... from the documentation for SELECT:

SELECT
    select_expr
    FROM table_references
    WHERE where_condition
    INTO var_name

I would strongly suspect that the order of these clauses is important. In your queries you mix up the order, and I think this might be giving the problems, although the error messages are misleading.

The WHERE clause should be before the INTO here:

    select p.rgt + 1 
    from page p 
    into @subject_new_lft 
    where p.page_id = reference_id;

and here the INTO and FROM are also reversed:

    select 
            p.lft, 
            p.rgt, 
            p.rgt - p.lft,
            p.rgt + 1 
    into 
            @subject_old_lft, 
            @subject_old_rgt, 
            @subject_width, 
            @subject_old_right_sibling_lft 
    from page p 
    where p.page_id = subject_id;

Try changing it round and see if it helps.