Skip to content

Instantly share code, notes, and snippets.

@ikennaokpala

ikennaokpala/UPDATE COUNT

Created March 3, 2011 17:05
Show Gist options
  • Save ikennaokpala/853106 to your computer and use it in GitHub Desktop.
Save ikennaokpala/853106 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
UPDATE COUNT
-- DELIMITER $$
DELIMITER $$
-- DROP PROCEDURE IF EXISTS UPDATE_COL_COUNT $$
DROP PROCEDURE IF EXISTS UPDATE_COL_COUNT $$
CREATE PROCEDURE UPDATE_COL_COUNT(NEW_TAXO_ID INTEGER, NEW_COUNT INTEGER)
BEGIN
SET @dyn_sql=CONCAT(
'UPDATE ' , 'WP_TERM_TAXONOMY',
' SET ' , 'COUNT', ' = ?
WHERE ' , 'TERM_TAXONOMY_ID', ' = ?');
PREPARE s1 FROM @dyn_sql;
SET @where_val=NEW_TAXO_ID;
SET @set_val=NEW_COUNT;
EXECUTE s1 USING @where_val,@set_val;
DEALLOCATE PREPARE s1;
END;
-- DROP PROCEDURE IF EXISTS UPDATECOUNT $$
DROP PROCEDURE IF EXISTS UPDATECOUNT;
CREATE PROCEDURE UPDATECOUNT()
BEGIN
-- DECLARE
DECLARE NEW_TAXO_ID, OLD_COUNT, NEW_COUNT INTEGER;
DECLARE NO_MORE INT DEFAULT 0;
-- DECLARE THE CURSOR
DECLARE CATEGORY_COUNT CURSOR FOR
SELECT WP_TERM_TAXONOMY.TERM_TAXONOMY_ID AS TAXO_ID,WP_TERM_TAXONOMY.COUNT AS COUNTER,
COUNT(*) AS COUNT FROM WP_TERM_TAXONOMY LEFT JOIN
WP_TERM_RELATIONSHIPS ON WP_TERM_TAXONOMY.TERM_TAXONOMY_ID =
WP_TERM_RELATIONSHIPS.TERM_TAXONOMY_ID WHERE
(WP_TERM_TAXONOMY.PARENT = 6 OR WP_TERM_TAXONOMY.TERM_ID = 6) GROUP BY TERM_ID FOR UPDATE;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET NO_MORE=1;
START TRANSACTION;
-- OPEN THE CURSOR
OPEN CATEGORY_COUNT;
-- LOOP THROUGH ALL ROWS
C_LOOP: LOOP
-- GET ORDER NUMBER
FETCH CATEGORY_COUNT INTO NEW_TAXO_ID, OLD_COUNT, NEW_COUNT;
-- IF
IF (NO_MORE=1) THEN LEAVE C_LOOP; END IF; -- END IF;
CALL UPDATE_COL_COUNT(NEW_TAXO_ID, NEW_COUNT);
END LOOP C_LOOP;
-- END OF LOOP
CLOSE CATEGORY_COUNT;
SET NO_MORE=0;
COMMIT;
END;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment