Mysql - delete from multiple tables with one query

Question

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

You can also use following query       DELETE FROM Student  Enrollment USING Student INNER JOIN Enrollment ON Student studentId   Enrollment studentId  WHERE Student studentId  51

User · Answer

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

User · Answer

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

User · Answer

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

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

Examples related to mysql