Delete with Join in MySQL

Question

Here is the script to create my tables    CREATE TABLE clients      client i INT 11      PRIMARY KEY  client id     CREATE TABLE projects      project id INT 11  UNSIGNED     client id INT 11  UNSIGNED     PRIMARY KEY  project id     CREATE TABLE posts      post id INT 11  UNSIGNED     project id INT 11  UNSIGNED     PRIMARY KEY  post id       In my PHP code  when deleting a client  I want to delete all projects posts   DELETE  FROM posts INNER JOIN projects ON projects project id   posts project id WHERE projects client id    client id    The posts table does not have a foreign key client id  only project id  I want to delete the posts in projects that have the passed client id   This is not working right now because no posts are deleted

User · Answer

If join does not work for you you may try this solution  It is for deleting orphan records from t1 when not using foreign keys   specific where condition  I e  it deletes records from table1  that have empty field  code  and that do not have records in table2  matching by field  name    delete table1 from table1 t1      where  t1 code           and 0  select count t2 name  from table2 t2 where t2 name t1 name

User · Answer

You just need to specify that you want to delete the entries from the posts table   DELETE posts FROM posts INNER JOIN projects ON projects project id   posts project id WHERE projects client id    client id   EDIT   For more information you can see this alternative answer

User · Answer

Or the same thing  with a slightly different  IMO friendlier  syntax   DELETE FROM posts  USING posts  projects  WHERE projects project id   posts project id AND projects client id    client id    BTW  with mysql using joins is almost always a way faster than subqueries

User · Answer

mysql gt  INSERT INTO tb1 VALUES 1 1   2 2   3 3   6 60   7 70   8 80    mysql gt  INSERT INTO tb2 VALUES 1 1   2 2   3 3   4 40   5 50   9 90     DELETE records  FROM one table    mysql gt  DELETE tb1 FROM tb1 tb2 WHERE tb1 id  tb2 id    DELETE RECORDS FROM both tables   mysql gt  DELETE tb2 tb1 FROM tb2 JOIN tb1 USING id

User · Answer

Try like below    DELETE posts   projects    FROM posts INNER JOIN projects ON projects project id   posts project id WHERE projects client id    client id

User · Answer

MySQL DELETE records with JOIN  You generally use INNER JOIN in the SELECT statement to select records from a table that have corresponding records in other tables  We can also use the INNER JOIN clause with the DELETE statement to delete records from a table and also the corresponding records in other tables e g   to delete records from both T1 and T2 tables that meet a particular condition  you use the following statement   DELETE T1  T2 FROM T1 INNER JOIN T2 ON T1 key   T2 key WHERE condition   Notice that you put table names T1 and T2 between DELETE and FROM  If you omit the T1 table  the DELETE statement only deletes records in the T2 table  and if you omit the T2 table  only records in the T1 table are deleted   The join condition T1 key   T2 key specifies the corresponding records in the T2 table that need be deleted   The condition in the WHERE clause specifies which records in the T1 and T2 that need to be deleted

User · Answer

Since you are selecting multiple tables  The table to delete from is no longer unambiguous  You need to select   DELETE posts FROM posts INNER JOIN projects ON projects project id   posts project id WHERE projects client id    client id   In this case  table name1 and table name2 are the same table  so this will work   DELETE projects FROM posts INNER JOIN         You can even delete from both tables if you wanted to   DELETE posts  projects FROM posts INNER JOIN         Note that order by and limit don t work for multi-table deletes   Also be aware that if you declare an alias for a table  you must use the alias when referring to the table   DELETE p FROM posts as p INNER JOIN           Contributions from Carpetsmoker and etc

User · Answer

-- Note that you can not use an alias over the table where you need delete  DELETE tbl pagos activos usuario FROM tbl pagos activos usuario  tbl usuarios b  tbl facturas c Where tbl pagos activos usuario usuario b cedula and tbl pagos activos usuario cod c cod and tbl pagos activos usuario rif c identificador and tbl pagos activos usuario usuario c pay for and tbl pagos activos usuario nconfppto c nconfppto and NOT ISNULL tbl pagos activos usuario nconfppto  and c estatus 50

User · Answer

I m more used to the subquery solution to this  but I have not tried it in MySQL   DELETE  FROM posts WHERE   project id IN               SELECT  project id             FROM    projects             WHERE   client id    client id

User · Answer

You can also use ALIAS like this it works just used it on my database  t is the table need deleting from   DELETE t FROM posts t INNER JOIN projects p ON t project id   p project id AND t client id   p client id

User · Answer

Single Table Delete   In order to delete entries from posts table   DELETE ps  FROM clients C  INNER JOIN projects pj ON C client id   pj client id INNER JOIN posts ps ON pj project id   ps project id WHERE C client id    client id    In order to delete entries from projects table   DELETE pj  FROM clients C  INNER JOIN projects pj ON C client id   pj client id INNER JOIN posts ps ON pj project id   ps project id WHERE C client id    client id    In order to delete entries from clients table   DELETE C FROM clients C  INNER JOIN projects pj ON C client id   pj client id INNER JOIN posts ps ON pj project id   ps project id WHERE C client id    client id      Multiple Tables Delete   In order to delete entries from multiple tables out of the joined results you need to specify the table names after DELETE as comma separated list   Suppose you want to delete entries from all the three tables  posts projects clients  for a particular client    DELETE C pj ps  FROM clients C  INNER JOIN projects pj ON C client id   pj client id INNER JOIN posts ps ON pj project id   ps project id WHERE C client id    client id

User · Answer

One solution is to use subquery DELETE FROM posts WHERE post id in  SELECT post id FROM posts p INNER JOIN projects prj ON p project id   prj project id  INNER JOIN clients c on prj client id   c client id WHERE c client id    client id      The subquery returns the ID that need to be deleted  all three tables are connected using joins and only those records are deleted that meets the filter condition  in yours case i e  client id in the where clause

User · Answer

Try this   DELETE posts   FROM posts INNER JOIN projects ON projects project id   posts project id WHERE projects client id    client id

User · Answer

Another method of deleting using a sub select that is better than using IN would be WHERE EXISTS  DELETE  FROM posts WHERE   EXISTS   SELECT  1                   FROM    projects                  WHERE   projects client id   posts client id     One reason to use this instead of the join is that a DELETE with JOIN forbids the use of LIMIT  If you wish to delete in blocks so as not to produce full table locks  you can add LIMIT use this DELETE WHERE EXISTS method

[mysql] Delete with Join in MySQL

Examples related to mysql