Running an SQL command on all tables of a database

[bwelch42]

Using MySQL 5.0 and only SQL, what's a way to run a SQL command on all tables of a database?

The command would be something like: delete userID from tblUser where userID = 'abcd1234';

 

[r937]

what if some of the tables don't have a userid? you'll get an error

best advice: browse the INFORMATION_SCHEMA.COLUMNS view, and build your SQL from that

r937.com | rudy.ca

 

[bwelch42]

Okay, I've got the list of tables that need updates. What looping structure works best? As I'm switching from Oracle and MS SQL Server, the looping part seems foreign.

 

[feherke]

Hi

Personally I would use Bash or Perl to read the input ( list generated according to r937's advice ) and generate the output ( an SQL script containing the [tt]delete[/tt] statements to execute ). I mean, I would use command line/desktop tools, not MySQL itself.

Feherke.

http://rootshell.be/~feherke/

 

[bwelch42]

I agree, Feherke. I have a shell script that does this already, but unfortunately have only SQL access to the server in question.

Oh, well. This will force me to develop my SQL skills.

 

[r937]

i use a simple text editor

how hard is it to change this --

orders.custid
customers.id
suppliers.id
products.supplier_id

to this --

delete from orders where custid = 'todd'
delete from customers where id = 'todd'
delete from suppliers where id = 'todd'
delete from products where supplier_id = 'todd'


r937.com | rudy.ca

 

[0ddball]

I know this doesn't help at all - but your database should have maintained these indicies itself. It should be dead easy to recursively delete a user.

I'm done finger waggin - flame me now


Yet another unchecked rambling brought to you by:
Oddball