serverstorm — 2012-07-11T14:29:32-04:00 — #1
A quick question, does anyone know if there is a way to drop tables with a specific name + wildcard;
I would like to delete multiple tables that begin with 'temp_' like 'temp_atyztyj' and 'temp_pqrtynr' Is there any drop table command that I can drop both these tables without knowing what comes after 'temp_'?
r937 — 2012-07-11T14:55:02-04:00 — #2
query information_schema.tables to get the table names, then use the results to issue your drops
system — 2012-07-11T14:56:59-04:00 — #3
You can use the catalog to generate the SQL DROP commands
SELECT 'DROP ' || [...]
and run the script after that.
EDIT: a split second too late...
serverstorm — 2012-07-11T15:00:58-04:00 — #4
serverstorm — 2012-07-11T18:22:10-04:00 — #5
So I got
"DROP TABLE ",
) AS stmt
WHERE TABLE_SCHEMA = "my_db" AND TABLE_NAME LIKE "__temp_%";
It generates a stmt like
DROP TABLE __temp_afsrpsy,__temp_dfsposb,__temp_exyrygx,__temp_gfyunsc,__temp_jhwshaj,__temp_khlmgnv,__temp_mhbhtfy,__temp_ondgdqr,__temp_oqucumc,__temp_prmgzax,__temp_qqhlwgi,__temp_vhrmdpu,__temp_xpqhrew,__temp_xraqrss,__temp_yvnplnu
This is OK as it is a two step process, one to generate the Drop statement and the other to copy and run this statement into the command line and run it; however is there a way to do this in one step in the command line or work_bench without using PHP?
Yes I'm curious and lazy
r937 — 2012-07-11T19:46:51-04:00 — #6
be aware that only mysql allows you to specify a list of tables in a single DROP statement
i really doubt it, but i'm forever surprised by what some people can come up with
serverstorm — 2012-07-11T20:24:47-04:00 — #7
Ok, I am testing a php object that in the end will do self cleanup of the temp table in question. I am not quite far enough along with it to drop the table as part of the routine as I still need to study the contents of the imported temp table so I will not be using this technique in production. I've used standard SQL for all SQL I use in the application and I don't plan on implementing any dialect features unless I'm really need it. Thank you for point this out
Ok, I will go with what I have an not be lazy
mittineague — 2014-09-20T15:48:58-04:00 — #8
This topic is now archived. It is frozen and cannot be changed in any way.