Unable to delete from multiple tables

Question

I'm trying to delete all records of a person in a website that I created through an admin account. The query is as followed:

DELETE FROM users u, user_address ua, pending_order po, product_catalogue pc 
WHERE u.user_id = ua.user_id 
AND ua.user_id = po.user_id 
AND po.user_id = pc.user_id 
AND u.user_id = '$user_id'

$user_id is obtained from an $_POST['user_id'] in a form.

I keep getting this error whenever I run it

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'u, user_address ua, pending_order po, product_catalogue pc WHERE u.user_id = ua.' at line 1

What's wrong here?

You cannot delete from multiple tables in one statement http://stackoverflow.com/questions/17539145/is-it-possible-to-delete-from-multiple-tables-in-the-same-sql-statement — Brian DeMilia, Aug 02 '14 at 17:19
Unless you've set up foreign keys with ON DELETE CASCADE, you can only delete from one table at a time — Mark Baker, Aug 02 '14 at 17:25

score 0 · Answer 1 · answered Aug 02 '14 at 17:21

You have to run one delete statement for each table:

delete from users where user_id = '$user_id';
delete from user_addresses where user_id = '$user_id';
delete from pending_order where user_id = '$user_id';
delete from product_catalogue where user_id = '$user_id';

score 0 · Answer 2 · answered Aug 02 '14 at 17:21

you must delete from tables one by one:

DELETE FROM users  WHERE user_id = '$user_id';
DELETE FROM user_address WHERE user_id = '$user_id';
DELETE FROM  pending_order WHERE user_id = '$user_id';
DELETE FROM product_catalogue WHERE user_id = '$user_id';

If you are use large tables and MySQL Replication, this can be better sometimes (test it!):

$rows =  SELECT group_concat(id) FROM product_catalogue WHERE user id = ...
//$rows  = 1,2,3,4,5,10,15,155 or similar list)
DELETE FROM table WHERE id in ( $rows )

I am doing this sometimes when the list of deleted items is large and I want to minimize locks and replication lag.

score 0 · Answer 3 · answered Aug 02 '14 at 17:24

As the documentation recommends... :)

DELETE FROM users u, user_address ua, pending_order po, product_catalogue pc 
using u inner join ua inner join po inner join pc
WHERE u.user_id = ua.user_id 
AND ua.user_id = po.user_id 
AND po.user_id = pc.user_id 
AND u.user_id = '$user_id';

Note: it might be less time-consuming to delete corresponding rows from four tables separately (if necessary, within one transaction). Especially if not in all of them user_id is an indexed field.

score 0 · Answer 4 · answered Aug 02 '14 at 17:32

You can't delete multiple tables with one statement delete statement..You need to do it seperately. It would be like

DELETE FROM users  WHERE user_id = '$user_id';
DELETE FROM user_address WHERE user_id = '$user_id';
DELETE FROM  pending_order WHERE user_id = '$user_id';
DELETE FROM product_catalogue WHERE user_id = '$user_id';

Abhishek · Answer 5 · 2014-08-03T04:36:51.197

you can run the following as suggested above

DELETE FROM users  WHERE user_id = '$user_id';
DELETE FROM user_address WHERE user_id = '$user_id';
DELETE FROM  pending_order WHERE user_id = '$user_id';
DELETE FROM product_catalogue WHERE user_id = '$user_id';

a. But why don't you setup Foreign key relations, to achieve your desired result.

you have to create user_id as the primary key in users table, and in the other tables user_address, pending_order, product_catalogue declare them as foreign keys and accordingly modify your query by adding ON DELETE CASCADE, this will delete all records with the associated with the passed $user_id in the child tables.

b. you can also setup MySQL Triggers, just google around for details on Triggers.

c. I have a different suggestion though.

Why don't you add a new column in your table called active_status and store the status of records as Active Inactive, so rather than deleting the record you just change the status of the records which you may refer to as a soft delete, use the same pk and fk concept in tables like on update cascade.

Unable to delete from multiple tables

5 Answers5