CODEWITHSUNDEEP
mysql
Nole
Nole

Reputation: 119

How to delete specific rows from all tables in a MySQL database?

I have a database named my_db. Each table in my_db has a column set_id that has values from 2140 to 2180. I would like to go through all the tables in my_db and delete the rows where set_id is greater than 2170. How do I do that?

Upvotes: 1

Views: 803

Answers (2)

TrebledJ
TrebledJ

Reputation: 9007

I like Marco's answer since it's short and provides a nice workaround. This is what I've come up with using Procedures, While-Loops, and Prepared-Statements. No copying is needed.

It retrieves and stores the into a temporary table, then loops through each table-name and performs individual DELETE statements.

DROP PROCEDURE IF EXISTS myPurge;

-- Will be used to signify the end of the procedure
DELIMITER ;;

-- Use a procedure
CREATE PROCEDURE myPurge()
BEGIN

    -- Retrieve tables from information_schema and
    -- Store them into a temporary table
    DROP TABLE IF EXISTS tempTables;
    CREATE TEMPORARY TABLE tempTables
        SELECT table_name FROM information_schema.tables WHERE table_schema = 'my_db';

    -- Initialise variables
    SET @i = 0;
    SELECT COUNT(*) FROM tempTables INTO @n;

    -- Loop from 0 to number of rows
    WHILE @i < @n DO

        -- Retrieve table from row @i
        -- SELECT * FROM tempTables LIMIT @i, 1 INTO @atable; -- doesn't seem to work on MySQL v5

        -- Prepare and execute a subsidiary query
        SELECT CONCAT("SELECT * FROM tempTables LIMIT ", @i, ",1 INTO @atable") INTO @preLimStmt;
        PREPARE limStmt FROM @preLimStmt;
        EXECUTE limStmt;

        -- Save statement into temporary variable
        -- HERE we prepare your PURGE
        SELECT CONCAT("DELETE FROM my_db.", @atable, " WHERE set_id > 2170") INTO @preStmt;

        -- Prepare and execute the purge statement
        PREPARE stmt FROM @preStmt;
        EXECUTE stmt;

        -- Increment @i
        SET @i = @i + 1;

    END WHILE;

END;;

-- Call the procedure
CALL myPurge();

-- cleanup
DEALLOCATE PREPARE limStmt;
DEALLOCATE PREPARE stmt;

-- Reset to default
DELIMITER ;

-- Remove the temporary table
DROP TABLE tempTables;

Note: I'm using MySQL version 5.7.23.

Upvotes: 0

Marco
Marco

Reputation: 587

I think, that is not a query for that, but you can do something like this

SELECT CONCAT('delete from my_db.',table_name,' where set_id > 270') FROM information_schema.tables where table_schema='my_db';

the result of that are all the queries that you need to run. you can copy and run it.

Upvotes: 1

Related Questions