wild card delete

[EchoFool]

Hey,

 

Quick question - is there a way to build a query so that it will delete all rows of data from any database / tablename from my server that has the field "UserID" and "UserID = 0" ?

 

Or do i have to be more precise in my MYSQL to do it which im hopeing not.

[Mchl]

Nope. You have to delete it from each table using separate query.

 

Alternatively, if you had proper constraints in place, MySQL would do all the work for you, but since you're asking this question, you probably don't have them.

[EchoFool]

Im not really a MYSQL expert so i don't know what you mean by proper constraints?

 

What about if i want to optimize all tables in all my databases?

[Mchl]

Here's manual entry on table constraints:

http://dev.mysql.com/doc/refman/5.1/en/innodb-foreign-key-constraints.html

 

They can be defined so that when you delete a row from 'parent' table (e.g. 'users') all rows in 'child' tables (for example 'messages') will be deleted automatically.

 

 

Anyway:

SELECT CONCAT(TABLE_SCHEMA,'.',TABLE_NAME) FROM `information_schema`.`COLUMNS` WHERE COLUMN_NAME = 'UserID';

 

will fetch you a list of all tables having 'UserID' column. Then you just need to run DELETE query on each of them. You should be able to do this in, say, 25 lines of PHP

[EchoFool]

Thanks much