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.
DELETE t1, t2 FROM t1 INNER JOIN t2 ON t1.id=t2.id INNER JOIN t3 ON t2.id=t3.id;
INNER JOIN to some other type of JOIN as appropriate
DELETE clause only specifies t1, t2 only those two tables will be affected.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 : https://dev.mysql.com/doc/refman/5.7/en/create-table-foreign-keys.html
CASCADE do not fire TRIGGERs. This means that if you have a TRIGGER on table A, and a FOREIGN KEY to a column in table B, and you do e.g. a DELETE in table B that CASCADEs to table A, the trigger on table A is not triggered. This can lead to severe bugs such as data loss, or ruining your data integrity. Do not use CASCADE with FOREIGN KEYs for this very reason, use RESTRICT instead (which is default if nothing else is specified).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!
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
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
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]
LOW_PRIORITY"? It means if I write DELETE table1, table2 FROM ... first the rows of table1 will delete and then the rows of table2 ?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.
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.
