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 to straightforward SQL. |
|