A cursor if necessary, but not necessarily a cursor

from the Artful Common Queries page


You have photos (id INT, photo BLOB, tally INT) and votes(id INT, userID INT, photoID INT) tables. You wish to update photos.tally values from counts per photo in the votes table. You can use a cursor to walk the photos table, updating the tally as you go:

DROP TABLE IF EXISTS photos;
CREATE TABLE photos (id INT, photo BLOB, tally INT); 
INSERT INTO photos VALUES(1,'',0),(2,'',0);
DROP TABLE IF EXISTS VOTES;
CREATE TABLE VOTES( userID INT, photoID INT);
INSERT INTO votes VALUES (1,1),(2,1),(2,2);

DROP PROCEDURE IF EXISTS updatetallies;
DELIMITER //
CREATE PROCEDURE updatetallies()
BEGIN
  DECLARE done INT DEFAULT 0;
  DECLARE pid INT;
  DECLARE cur1 CURSOR FOR SELECT id FROM photos;
  DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
  OPEN cur1;
  FETCH cur1 INTO pid;
  WHILE done = 0 DO
    UPDATE photos 
      SET tally = (SELECT COUNT(*) FROM votes WHERE photoid = pid ) 
      WHERE id = pid;
    FETCH cur1 INTO pid;
  END WHILE;
  CLOSE cur1;
  SELECT id,tally FROM photos;
END //
DELIMITER ;
CALL updatetallies();
+------+-------+
| id   | tally |
+------+-------+
|    1 |     2 |
|    2 |     1 |
+------+-------+

but a simple join does exactly the same job at much less cost:

UPDATE photos 
SET tally = (
  SELECT COUNT(*) FROM votes WHERE votes.photoid = photos.id
);

Before you burden your app with a cursor, see if you can simplify the processing to a straightforward join.

Return to the Artful Common Queries page