Mysql - delete from multiple tables with one query [duplicate]

I have 4 tables that stores different information about a user in each. Each table has a field with user_id to identify which row belongs to which user. If I want to delete the user is this the best way to delete that users information from multiple tables? My objective is to do it in one query.

Query:

"DELETE FROM table1 WHERE user_id='$user_id';
DELETE FROM table2 WHERE user_id='$user_id';
DELETE FROM table3 WHERE user_id='$user_id';
DELETE FROM table4 WHERE user_id='$user_id';";

Apparently, it is possible. From the manual:

You can specify multiple tables in a DELETE statement to delete rows from one or more tables depending on the particular condition in the WHERE clause. However, you cannot use ORDER BY or LIMIT in a multiple-table DELETE. The table_references clause lists the tables involved in the join. Its syntax is described in Section 12.2.8.1, “JOIN Syntax”.

The example in the manual is:

DELETE t1, t2 FROM t1 INNER JOIN t2 INNER JOIN t3
WHERE t1.id=t2.id AND t2.id=t3.id;

should be applicable 1:1.

You can define foreign key constraints on the tables with ON DELETE CASCADE option.

Then deleting the record from parent table removes the records from child tables.

Check this link : http://dev.mysql.com/doc/refman/5.5/en/innodb-foreign-key-constraints.html

You can also use following query :

DELETE FROM Student, Enrollment USING Student INNER JOIN Enrollment ON Student.studentId = Enrollment.studentId WHERE Student.studentId= 51;

A join statement is unnecessarily complicated in this situation. The original question only deals with deleting records for a given user from multiple tables at the same time. Intuitively, you might expect something like this to work:

DELETE FROM table1,table2,table3,table4 WHERE user_id='$user_id'

Of course, it doesn't. But rather than writing multiple statements (redundant and inefficient), using joins (difficult for novices), or foreign keys (even more difficult for novices and not available in all engines or existing datasets) you could simplify your code with a LOOP!

As a basic example using PHP (where $db is your connection handle):

$tables = array("table1","table2","table3","table4");
foreach($tables as $table) {
  $query = "DELETE FROM $table WHERE user_id='$user_id'";
  mysqli_query($db,$query);
}

Hope this helps someone!