Deleting one record from all tables in mysql database

Question

I am not an expert on mysql. I have a database with over 500 tables and I want to know if there is a way search for a certain record in each table and delete it from table if it exists. Thanks in advance

possible duplicate of [Delete data from all tables in MYSQL](http://stackoverflow.com/questions/1885101/delete-data-from-all-tables-in-mysql) — Lix, Sep 23 '12 at 15:34
I dont want to delete all tables or all records. I have a function which takes an array of account numbers and deletes the record from the database but how can I loop thru 500+ tables and check if the account number exists and if it does delete it from that table — Raymond Feliciano, Sep 23 '12 at 15:41

score 1 · Answer 1 · answered Sep 23 '12 at 15:37

Take a look at the following stored procedure that you can create :

CREATE PROCEDURE procDeleteAllTables()

BEGIN
        DECLARE table_name VARCHAR(255);
        DECLARE end_of_tables INT DEFAULT 0;

        DECLARE cur CURSOR FOR 
            SELECT t.table_name 
            FROM information_schema.tables t 
            WHERE t.table_schema = DATABASE() AND t.table_type='BASE TABLE';
        DECLARE CONTINUE HANDLER FOR NOT FOUND SET end_of_tables = 1;

        SET FOREIGN_KEY_CHECKS = 0;
        OPEN cur;

        tables_loop: LOOP
            FETCH cur INTO table_name;

            IF end_of_tables = 1 THEN
                LEAVE tables_loop;
            END IF;

            SET @s =  CONCAT('DELETE FROM ' , table_name);
            PREPARE stmt FROM @s;
            EXECUTE stmt;

        END LOOP;

        CLOSE cur;
        SET FOREIGN_KEY_CHECKS = 1;
    END

If you need to insert a check for a specific record you can add a WHERE clause in this way :

SET @s =  CONCAT(CONCAT('DELETE FROM ' , table_name), ' WHERE somefield = 1');

Teena Thomas · Accepted Answer · 2012-09-23T15:56:55.593

something similar to this. make the changes as per your need.

         $tables = mysql_query('show tables', $conn);
         foreach( $acc_array as $acc)
           foreach( $tables as $v) {
           $result1 = mysql_fetch_assoc(mysql_query("select account_num from $v where account_num = '$acc'" , $conn));
          if( !empty($result1) )
            $result2 = mysql_query("delete from $v where account_num = '$acc'" , $conn);
         }

score 0 · Answer 3 · answered Sep 23 '12 at 17:24

$sql = "SHOW TABLES FROM dbname";
    $result = mysql_query($sql);

    if (!$result) {
        echo "DB Error, could not list tables\n";
        echo 'MySQL Error: ' . mysql_error();
        exit;
    }

    while ($row = mysql_fetch_row($result)) {


        echo "Table: {$row[0]}\n";
        $tablename=$row[0];
        $sql="DELETE FROM $tablename WHERE name='ddddd'";
        echo "Table: {$sql}\n";
        $res=mysql_query($sql);

    }

Deleting one record from all tables in mysql database

3 Answers3