Global Delete

[laPistola]

MySQL 5.1

 

Is there a command that can find every table that contains a given field name and then delete every row that contains a given value within that field.

 

I know the below don't work (after trying) but it may help explain what im look at doing.

 

DELETE FROM * WHERE id = 1

 

Thank you.

[sunfighter]

It's

mysql_query("DELETE FROM 'table_name' WHERE 'column_name'='what_your_looking_for'");

[fenway]

Across tables? I suppose you could use the information_schema to determine which tables contain a given field -- but why on earth would you want to do this?

[laPistola]

Im writing some software for a training company and one of the features is to delete a student from the system. Now just deleting a student from the students DB table would cause errors as the information regarding what course they was on and which units had been assigned to them and IV forms and list goes on. All this would still be in the DB and come up on some pages within the application, but when clicked on an error would appear as it wouldn't be able to find the student in the DB. So instead of doing a few queries to target and delete the rows that contain there id from each table, I wondered if there was a faster way not just to write it (this way would only take 5 mins to code anyway) but server load times. There are a few times where a command such as I have inquired about would be an advantage. Mostly when deleting information.

[laPistola]

So if im right you are saying the only real way would be to run a SELECT command on the COLUMNS table within the information schema eg

 

SELECT TABLE_NAME FROM COLUMNS WHERE COLUMN_NAME = 'sid'

 

Then run a loop that does a query on each table_name returned?

 

If so this would run one more query then if I just manually told the script which tables to target.

[ebmigue]

So if im right you are saying the only real way would be to run a SELECT command on the COLUMNS table within the information schema eg

 

SELECT TABLE_NAME FROM COLUMNS WHERE COLUMN_NAME = 'sid'

 

Then run a loop that does a query on each table_name returned?

 

If so this would run one more query then if I just manually told the script which tables to target.

Would do the same thing.

The advantage of doing the deletion per table on a loop than by manually doing it, is that the whole functionality is automated. If in the future, a new table with the field(s) of your interest is introduced, you need not edit your script.

[laPistola]

Yes I was thinking about doing it anyway to make the software more flexible with future expansion.  Even more so to prevent more tables getting created that stores the sid and I or someone forgets to add it to the delete scripts.