CODEWITHSUNDEEP
mysqlsql-delete
MacMac
MacMac

Reputation: 35361

Delete from all tables by a particular ID

I've searched over SO, but many questions are related to truncating the tables, but I don't want to empty the tables, I need to delete from all tables, perhaps with one query instead of multiple queries.

So far I have 34 tables in my database, I need to delete all rows that have an a particular ID when requested. For example:

DELETE FROM table1, table2 ... table33, table34
WHERE customer_id = 4

How do I go ahead with a query that can do this? On another thought, all tables may not have rows that is same to the customer_id, so it should delete even there are no records with an ID of 4.

Upvotes: 0

Views: 5094

Answers (2)

Dave
Dave

Reputation: 6189

The reason you can't find this is because you shouldn't really need it. I don't think there is a way to do this because usually you don't have to if you properly use foreign key constraints. The proper way to maintain this is to have your foreign key constraints set up so they point from tables which have your customer id (ex customer_purchase) to the table where custom_id is the primary key; and then when you delete a customer from the table where customer_id is the primary key; all other tables referring to that customer will also be deleted.

Building from what I just mentioned; a lot of people choose to do a "soft" delete. This is where you just set a flag on the customer table and then simply set it to "1" when it is deleted. The reason you might want to do this is for audit-ability reasons. Say, for example, you need to keep a record of all purchases; even when a customer is deleted. In this situation; you should do soft deletes so you don't lose items from your purchase history when your customer is deleted.

Upvotes: 2

boobiq
boobiq

Reputation: 3024

I don't know if this is acceptable solution for you, but you can you

DELETE t1, t2, ... FROM table1 t1, table2 t2, ...
WHERE t1.customer_id = 4 AND t2.customer_id = 4 AND ...

Upvotes: 1

Related Questions