0

I am trying to drop tables with wp_ prefix but its giving error below

Error 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE TABLE LIKE 'wp_%'' at line 1

Here is my query

"DROP TABLE WHERE TABLE LIKE '{$wp}%'"

What is wrong in this query? Please help

Midhun MP
  • 103,496
  • 31
  • 153
  • 200
RDev
  • 35
  • 4

1 Answers1

0

As far as I know, you can't selectively delete tables. You have to specifically delete each table, since deletion can't use a filter. You could probably use the metadata to get the names of all of your tables, and then find out in your code which ones start with wp_. Then, you would just loop through your list of tables to delete and then delete them with drop table [table-name];.

To get the list of table names from metadata, use select table_name from information_schema.tables;.

Jashaszun
  • 9,207
  • 3
  • 29
  • 57
  • okay so information_schema.tables will only check in current database? – RDev Jul 13 '13 at 21:07
  • I'm pretty sure, yes. If you want to change the database you're looking at, then use `use [database-name];`. – Jashaszun Jul 13 '13 at 21:07
  • No no, I just want to do in current database. – RDev Jul 13 '13 at 21:08
  • I have tried this code but it's not deleting tables but only showing.`SELECT CONCAT('DROP TABLE ',table_name,';') FROM information_schema.tables WHERE table_name LIKE '{$wp}%'` – RDev Jul 13 '13 at 21:11
  • I'm not too sure about that query... I don't know that much about MySQL (only a little more than needed to answer your question). Take a look at [SELECT with CONCAT](http://stackoverflow.com/questions/5734570/mysql-select-with-concat-condition) for help. – Jashaszun Jul 13 '13 at 21:14