Advertisement
Advertisement


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.

2018/10/26
1
507
10/26/2018 2:07:35 PM

Accepted 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

2017/05/23
1269
5/23/2017 11:54:58 AM

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.

2019/05/16

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

2011/09/01

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
2014/03/15

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
        );
2009/03/17

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
2016/08/14

Source: https://stackoverflow.com/questions/652770
Licensed under: CC-BY-SA with attribution
Not affiliated with: Stack Overflow
Email: [email protected]