[mysql] Delete with Join in MySQL

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.

Try like below:

DELETE posts.*,projects.* 
FROM posts
INNER JOIN projects ON projects.project_id = posts.project_id
WHERE projects.client_id = :client_id;

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...

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.

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);

-- 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

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> INSERT INTO tb1 VALUES(1,1),(2,2),(3,3),(6,60),(7,70),(8,80);

mysql> INSERT INTO tb2 VALUES(1,1),(2,2),(3,3),(4,40),(5,50),(9,90);

DELETE records FROM one table :

mysql> DELETE tb1 FROM tb1,tb2 WHERE tb1.id= tb2.id;

DELETE RECORDS FROM both tables:

mysql> DELETE tb2,tb1 FROM tb2 JOIN tb1 USING(id);

Try this,

DELETE posts.*
FROM posts
INNER JOIN projects ON projects.project_id = posts.project_id
WHERE projects.client_id = :client_id

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

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.}

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).

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

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
        );