CODEWITHSUNDEEP
mysqldelete-row
Rana Ghosh
Rana Ghosh

Reputation: 4674

Delete all row from all table from MYSQL DB with some ID clause

Is it possible to delete all rows from all tables in MYSQL DB with some id = '123'.

Actually I needed this because there is lots of junk data in others table but in main table rows are deleted, I have the IDs.

Now the new logic is implemented in the right way but I have to delete previous rows. Is it possible in a single MYSQL query?

There is no DBA level cascading relationship.

Thanks in advance

Upvotes: 1

Views: 374

Answers (1)

r0xette
r0xette

Reputation: 908

I am giving you output based on my database which has multiple tables having column Id. So replace my db 'djangogirls' with your-database-name.

SQL Query (Replace djangogirls with your database name)

SELECT CONCAT('DELETE FROM ',TABLE_NAME," WHERE Id = '1';") FROM information_schema.COLUMNS 
WHERE TABLE_SCHEMA = 'djangogirls'
AND COLUMN_NAME ='Id';

The output for above will be a list of sql delete statement which you can copy paste and simply run it.

Output

DELETE FROM auth_group WHERE Id = '1';
DELETE FROM auth_group_permissions WHERE Id = '1';
DELETE FROM auth_permission WHERE Id = '1';
DELETE FROM auth_user WHERE Id = '1';
DELETE FROM auth_user_groups WHERE Id = '1';
DELETE FROM auth_user_user_permissions WHERE Id = '1';
DELETE FROM blog_post WHERE Id = '1';
DELETE FROM django_admin_log WHERE Id = '1';
DELETE FROM django_content_type WHERE Id = '1';
DELETE FROM django_migrations WHERE Id = '1';

Again make sure that you know exactly what you are doing. Better make backup using mysqldump -u root -p yourdb > yourdb.sql command.

Upvotes: 1

Related Questions