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

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';";

You can also use following query :

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

The documentation for DELETE tells you the multi-table syntax.

DELETE [LOW_PRIORITY] [QUICK] [IGNORE]
    tbl_name[.*] [, tbl_name[.*]] ...
    FROM table_references
    [WHERE where_condition]

Or:

DELETE [LOW_PRIORITY] [QUICK] [IGNORE]
    FROM tbl_name[.*] [, tbl_name[.*]] ...
    USING table_references
    [WHERE where_condition]

Normally you can't DELETE from multiple tables at once, unless you'll use JOINs as shown in other answers.

However if all yours tables starts with certain name, then this query will generate query which would do that task:

SELECT CONCAT('DELETE FROM ', GROUP_CONCAT(TABLE_NAME SEPARATOR ' WHERE user_id=123;DELETE FROM ') , 'FROM table1;' ) AS statement FROM information_schema.TABLES WHERE TABLE_NAME LIKE 'table%'

then pipe it (in shell) into mysql command for execution.

For example it'll generate something like:

DELETE FROM table1 WHERE user_id=123;
DELETE FROM table2 WHERE user_id=123;
DELETE FROM table3 WHERE user_id=123;

More shell oriented example would be:

echo "SHOW TABLES LIKE 'table%'" | mysql | tail -n +2 | xargs -L1 -I% echo "DELETE FROM % WHERE user_id=123;" | mysql -v

If you want to use only MySQL for that, you can think of more advanced query, such as this:

SET @TABLES = (SELECT GROUP_CONCAT(TABLE_NAME) FROM information_schema.TABLES WHERE TABLE_NAME LIKE 'table%');
PREPARE drop_statement FROM 'DELETE FROM @tables';
EXECUTE drop_statement USING @TABLES;
DEALLOCATE PREPARE drop_statement;

The above example is based on: MySQL – Delete/Drop all tables with specific prefix.

You can use following query to delete rows from multiple tables,

DELETE table1, table2, table3 FROM table1 INNER JOIN table2 INNER JOIN table3 WHERE table1.userid = table2.userid AND table2.userid = table3.userid AND table1.userid=3

from two tables with foreign key you can try this Query:

DELETE T1, T2
FROM T1
INNER JOIN T2 ON T1.key = T2.key
WHERE condition

usually, i would expect this as a 'cascading delete' enforced in a trigger, you would only need to delete the main record, then all the depepndent records would be deleted by the trigger logic.

this logic would be similar to what you have written.

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.

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!