Hi,
i have to write a mysql procedure to purge data from tables. but written procedure clear entire tables data. Please give me the solution for purging data.
CREATE DEFINER=`root`@`localhost` PROCEDURE `purgedata_main`() BEGIN DECLARE listID BIGINT DEFAULT -1; DECLARE reserveID BIGINT DEFAULT -1; DECLARE CPIN BIGINT DEFAULT -1; DECLARE listCount INT DEFAULT 0; DECLARE reserveCount INT DEFAULT 0; DECLARE done INT DEFAULT 0; DECLARE confRefNo INT DEFAULT 0; DECLARE pinCount INT DEFAULT 0; DECLARE loopCount INT; DECLARE startDate DATE DEFAULT '0000-00-00'; DECLARE cur CURSOR FOR SELECT confrefno,listid,chairperson_pinno,reservationid FROM movedata_table; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1; OPEN cur; SET loopCount = 0; read_loop: LOOP FETCH cur INTO confRefNo,listID,CPIN,reserveID; IF done THEN LEAVE read_loop; END IF; DELETE FROM attendance_table WHERE confrefno= confRefNo; DELETE FROM codr_detail_table WHERE confrefno= confRefNo; DELETE FROM recordfile_table WHERE confrefno= confRefNo; DELETE FROM reminder_table WHERE confrefno= confRefNo; DELETE FROM operatorreminder_table WHERE confrefno= confRefNo; DELETE FROM qa_table WHERE confrefno= confRefNo; DELETE FROM helpqueue_table WHERE confrefno= confRefNo; DELETE FROM conferencerunningengine_table WHERE confrefno= confRefNo; DELETE FROM odo_details_table WHERE confrefno= confRefNo; DELETE FROM running_conference_cos_table WHERE confrefno= confRefNo; DELETE FROM conference_table WHERE confrefno= confRefNo; SELECT COUNT(listid) INTO listCount FROM conference_table WHERE listid=listID; IF listCount = 0 THEN DELETE FROM conferee_table WHERE listid=listID; DELETE FROM listid_table WHERE listid=listID; END IF; SELECT COUNT(chairperson_pinno) INTO pinCount FROM conference_table WHERE chairperson_pinno=CPIN; IF pinCount = 0 THEN DELETE FROM pinnumber_table WHERE chairperson_pinno=CPIN; END IF; SELECT COUNT(reservationid) INTO reserveCount FROM conference_table WHERE reservationid=reserveID; IF reserveCount = 0 THEN DELETE FROM conference_schedule_table WHERE reservationid=reserveID; DELETE FROM requisitionid_table WHERE reservationid=reserveID; DELETE FROM uid_table WHERE reservationid=reserveID; DELETE FROM reservation_table WHERE reservationid=reserveID; END IF; END LOOP; CLOSE cur; END$$
Ads