CODEWITHSUNDEEP
mysqlstored-procedures
ColinM
ColinM

Reputation: 13936

MySQL incrementing variable in stored procedure

So I have a routine that does (pseudo-code):

$rows = SELECT DISTINCT a, b FROM t1;
foreach($rows as $row) {
    SET @i = 0;
    UPDATE t1 SET c_index = @i := (@i+1)
      WHERE a = $row[a] and b = $row[b] ORDER BY c DESC;
}

The point is to number a subset of rows by the way they are sorted. Works fine, but this is update runs thousands of times so is pretty slow. To speed it up I want to put it in a stored procedure.

BEGIN
  DECLARE done INT DEFAULT 0;
  DECLARE vA, vB, i INT;
  DECLARE cur1 CURSOR FOR
    SELECT DISTINCT a, b
    FROM t1 WHERE ...;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

  OPEN cur1;
  WHILE done = 0 DO
    FETCH cur1 INTO vA, vB;
    IF done = 0 THEN
      SET i=0;
      UPDATE t1
        SET c_index = i:= (i+1)
        WHERE a = vA AND b = vB
        ORDER BY c DESC;
    END IF;
  END WHILE;
  CLOSE cur1;
END

However, when creating the above procedure MySQL says there is a syntax error at "i := ...". If I use a session variable for 'i' it works (@i). Using a session variable in a stored procedure is not very elegant. How can the above stored procedure be fixed without using a session variable for 'i'?

Upvotes: 0

Views: 3784

Answers (1)

peterm
peterm

Reputation: 92785

UPDATED: Since MySQL cursors are not updatable (read-only) there is no much sense of using one in your case. Almost everything you can possibly think of can be expressed just by pure UPDATE statement(s).

If (a, b, c) are unique you can do it this way 

UPDATE table1 t JOIN 
(
  SELECT a, b, c, 
  (
    SELECT 1 + COUNT(*)
      FROM table1
     WHERE a = t.a
       AND b = t.b
       AND c > t.c
  ) rnum
    FROM table1 t
) s
    ON t.a = s.a
   AND t.b = s.b
   AND t.c = s.c
   SET t.c_index = s.rnum

Here is SQLFiddle demo

or if you happen to have some sort of id (e.g. auto_increment column) then

UPDATE table1 t JOIN 
(
  SELECT id, 
  (
    SELECT 1 + COUNT(*)
      FROM table1
     WHERE a = t.a
       AND b = t.b
       AND c > t.c
  ) rnum
    FROM table1 t
) s
    ON t.id = s.id
   SET t.c_index = s.rnum

Here is SQLFiddle demo

Upvotes: 1

Related Questions