Latest Tutorials| Questions and Answers|Ask Questions?|Site Map



Home Answers Viewqa SQL Purge Data from Mysql tables
Login         

View Questions and Answers by Category

Advertisements


 
Have Programming Question? Ask it here!
 
 
 


Venkatesan
Purge Data from Mysql tables
0 Answer(s)      2 years and 11 months ago
Posted in : SQL


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$$

Advertisement
View Answers

Related Tutorials/Questions & Answers:
Purge Data from Mysql tables
Purge Data from Mysql tables  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
mysql select from multiple tables
mysql select from multiple tables   how can i select the date field from multiple tables in mysql?   "UNION" can be used to select data from multiple tables in my sql... for example (SELECT * from name where `name
Advertisements
sql query to get data from two tables
sql query to get data from two tables  how can i get the data from two different tables?   Hi Friend, Please visit the following link: JOIN Query Simple Query Thanks
Data fetch from multiple SQL tables - Hibernate
that would use table classes to retrieve data from 8 or 9 tables. Any example of HQL fetching data from multiple tables would be a help. I have tried...Data fetch from multiple SQL tables   I am in the process of writing
Mysql List Tables
Mysql List Tables       The Tutorial illustrate an example from 'Mysql  List Tables'. To understand this example we use show tables query that return the list
Retrieve The Data From MySql Datbase
Retrieve The Data From MySql Datbase   How to Retrieve The Data From MYSQL database TO Use Select the Emp_id Option.And Also Search Option
how to retrieve data from multiple tables in jsp using javabeans
how to retrieve data from multiple tables in jsp using javabeans   ... for retrieving data fromm two tables : first table having fields: bookid,name...).second .java file for getting data from second table- package pack; public
how to retrieve data from multiple tables in jsp using javabeans
how to retrieve data from multiple tables in jsp using javabeans   ... for retrieving data fromm two tables : first table having fields: bookid,name...).second .java file for getting data from second table- package pack; public
how to retrieve data from multiple tables in jsp using javabeans
how to retrieve data from multiple tables in jsp using javabeans   ... for retrieving data fromm two tables : first table having fields: bookid,name...).second .java file for getting data from second table- package pack; public
Selecting particular data from multiple tables at database and perfoming calculations
Selecting particular data from multiple tables at database and perfoming... the output based on month) .. I have created 12 tables with table name - month1,month2,....,upto month12.. each table contains the data like name,amount,paiddate
How to Generate XML files from the data in DB tables?
How to Generate XML files from the data in DB tables?  HI Experts, I...").newInstance(); Connection conn = DriverManager.getConnection("jdbc:mysql...(); ResultSet rs=st.executeQuery("select * from employee where id=1
Mysql List Tables
Mysql List Tables       The Tutorial illustrate an example from 'Mysql  List Tables'. To understand this example we use show tables query that return the list
store data from a variable in mysql?
store data from a variable in mysql?  sir last time asked you tell me how to retrieve data from a database mysql and store it in an int variable... of the calculation from an int variable into mysql in a new table of database. how
retrieve data from mysql database
retrieve data from mysql database  hi am not familiar in php.....even... selected value on combobox which is to be retrieve the relevant data from mysql...;/html> retcombosearch.php form is <?php mysql_connect ("localhost
Data retrieve from mysql database
Data retrieve from mysql database  Hi sir, please give some example of jsp code for retrieving mysql database values in multiple dropdown list... from the dropdown, related data will get displayed on the textboxes. Here we have
php import data from excel to mysql
php import data from excel to mysql  php import data from excel to mysql
Different tables present in MySQL
Different tables present in MySQL  What are the different tables present in MySQL? Which type of table is generated when we are creating a table...; Hi friends, Total 5 types of tables we can create 1. MyISAM 2. Heap 3
Fetch the data from mysql and display it on php form
Fetch the data from mysql and display it on php form  when i press on login button, after succesful login the related data of that person should be display in other textbox
displaying data based on criteria from 2 tables having same type of data - Java Beginners
displaying data based on criteria from 2 tables having same type of data  Dear Experts, First, thanks for replying my question regarding my...: NameContactSubjects We have created following database tables: 1
MYSQL retrieve record from Data table
MYSQL retrieve record from Data table  Hi. I have a field in database named stages. its datatype is varchar(60). It contains values chennai,trichy,kanchipuram for a single record. I have to retrieve these data from the field
Exporting data from mysql into csv using jsp
Exporting data from mysql into csv using jsp  Hi friends.... I want to export the data from mysql to csv file using... i am having 30 columns in my database.. Eg- text1,text2,text3,....,upto text30... i want to export this data
retrieve data from mysql database and store it in a variable ?
retrieve data from mysql database and store it in a variable ?  sir , I am working on a project , in which I have to apply operation on input data which is stored in mysql. so to apply some arithmetic operation on it we have
Exporting data from mysql to csv file
Exporting data from mysql to csv file  Hi friends.... I want to export the data from mysql to csv file... i am having 30 columns in my database.. Eg... example that retrieves the data from the database and save it into csv file
extract data from excel sheet to mysql
extract data from excel sheet to mysql  sir, i want to extract data from excel sheet and save the data in mysql5.0 database in the form of table
how to store JTree data hierarchically in mysql database from netbeans
how to store JTree data hierarchically in mysql database from netbeans  how to store JTree data hierarchically in mysql database from netbeans. I am new to this topics so I need a program and tables you are using in database
mysql tables - JDBC
mysql tables  hi, I have a table in MySql, having fields, emp... mangager for MySQL, statements, preparedstatements,resultset. Please can you..."); con = DriverManager.getConnection("jdbc:mysql://localhost:3306/databasename
access data from mysql through struts
access data from mysql through struts  I am Pradeep Kundu. I am making a program in struts in which i want to access data from MySQL through struts. I am using Strut 1.3.8 , Netbean 6.7.1 and MySQL 5.5. In this program ,I want
Retriving data from MYSQL without line break using java
Retriving data from MYSQL without line break using java  get data without line breaking from mysql table (i.e data stored as mediumtext )using java
sending data to google chart api from mysql database using java
sending data to google chart api from mysql database using java  how to send data from mysql database to google chart api using java
Mysql Join 3 Tables
an example from 'Mysql Join 3 Tables' using Left Join. To grasp this example... Mysql Join 3 Tables       Mysql Join 3 Tables is used to join 3 Tables using left join
create dropdown cell in csv or excell from mysql data in php
create dropdown cell in csv or excell from mysql data in php  Hello sir i want to create drodown cell in csv from mysql data is this is possible or not? if this is posible please share answer or reference link Thanks
Advertisements
 

 

 

DMCA.com