Delete from multiple tabes where table names are in array

Question

** Tile maybe incorrect**

Without listing by hand the table names, could I use a function to get the tables names then use that in the delete statement? ie

DELETE FROM IN (
  SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE';
) AS T
WHERE
  T.ColumnName = 'value';

Is there a nice and clear way to do this? I'm not dropping any tables here.

No, that's not possible as easy as you envision. You'd need dynamic SQL for that, i.e. some code that generates and executes multiple `DELETE` statements. — sticky bit, Mar 22 '21 at 14:22
Hi. Is it something like this answer? https://stackoverflow.com/questions/31272326/how-to-clear-all-tables-selected-through-a-information-schema-tables-query-in-sq — Sylar, Mar 22 '21 at 14:23
In general yes, but the other question seems to be about SQL Server not MySQL. But the logic would be equivalent, yes. — sticky bit, Mar 22 '21 at 14:25

Sylar · Accepted Answer · 2021-03-22T15:52:05.960

In addition to Ross Smith's answer, I've made an addition to the table name look up.

delimiter //

drop procedure if exists hunt //
create procedure hunt()
begin
    DECLARE done BOOL default false;
    DECLARE tablename CHAR(255);
    
    -- This was the only bit I've added
    DECLARE cur1 cursor for (
        SELECT TABLE_NAME
            FROM information_schema.COLUMNS 
                WHERE 
                    TABLE_SCHEMA = 'schema' 
                AND COLUMN_NAME = 'column_name'
    );
    -- End what I've added

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    open cur1;

    myloop: loop
        fetch cur1 into tablename;
        if done then
            leave myloop;
        end if;
        -- Then my delete query
        set @sql = CONCAT('DELETE FROM ', tablename, ' WHERE 'column_name' = 'value'');
        prepare stmt from @sql;
        execute stmt;
        drop prepare stmt;
    end loop;

    close cur1;
end //

delimiter ;

call hunt();

Delete from multiple tabes where table names are in array

1 Answers1