在mysql中使用join删除

wixjitnu  于 2021-06-18  发布在  Mysql
关注(0)|答案(14)|浏览(261)

以下是创建我的表的脚本:

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

在我的php代码中,删除客户机时,我希望删除所有项目帖子:

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

posts表没有外键 client_id ,仅限 project_id . 我想删除已通过的项目中的帖子 client_id .
因为没有帖子被删除,所以现在不起作用。

qij5mzcb

qij5mzcb1#

我更习惯于这种子查询解决方案,但我没有在mysql中尝试过:

DELETE  FROM posts
WHERE   project_id IN (
            SELECT  project_id
            FROM    projects
            WHERE   client_id = :client_id
        );
gz5pxeao

gz5pxeao2#

一种解决方案是使用子查询

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

子查询返回需要删除的id;所有三个表都使用联接进行连接,只有那些符合筛选条件的记录(在您的情况下,即where子句中的client\ id)才会被删除。

xxslljrj

xxslljrj3#

mysql使用join删除记录
通常在select语句中使用inner join从一个表中选择在其他表中有相应记录的记录。我们还可以将inner join子句与delete语句一起使用,从表中删除记录,也可以删除其他表中的相应记录。例如,要从满足特定条件的t1和t2表中删除记录,请使用以下语句:

DELETE T1, T2
FROM T1
INNER JOIN T2 ON T1.key = T2.key
WHERE condition

注意,您将表名t1和t2放在delete和from之间。如果省略t1表,delete语句只删除t2表中的记录,如果省略t2表,则只删除t1表中的记录。
连接条件t1.key=t2.key指定t2表中需要删除的相应记录。
where子句中的条件指定了t1和t2中需要删除的记录。

kxe2p93d

kxe2p93d4#

尝试以下方式:

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

ego6inou5#

单表删除:
为了从 posts 表格:

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;

为了从 projects 表格:

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;

为了从 clients 表格:

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;

删除多个表:
为了从联接结果中删除多个表中的条目,您需要在之后指定表名 DELETE 以逗号分隔的列表:
假设要从所有三个表中删除条目( posts , projects , clients )对于特定客户:

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
weylhg0b

weylhg0b6#

试试这个,

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

kpbwa7wx7#

--请注意,不能在需要删除的表上使用别名

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
hvvq6cgz

hvvq6cgz8#

您只需要指定要从 posts 表格:

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

编辑:有关更多信息,请参阅此备选答案

nwwlzxa7

nwwlzxa79#

你也可以像这样使用别名它的工作只是用它在我的数据库!t是需要删除的表!

DELETE t FROM posts t
INNER JOIN projects p ON t.project_id = p.project_id
AND t.client_id = p.client_id
iezvtpos

iezvtpos10#

由于要选择多个表,因此要从中删除的表不再是明确的。您需要选择:

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

在这种情况下, table_name1 以及 table_name2 是同一张表,所以这将起作用:

DELETE projects FROM posts INNER JOIN [...]

如果需要,甚至可以从两个表中删除:

DELETE posts, projects FROM posts INNER JOIN [...]

请注意 order by 以及 limit 不适用于多表删除。
还要注意,如果为表声明别名,则在引用表时必须使用别名:

DELETE p FROM posts as p INNER JOIN [...]

吸烟者等的贡献。

xoshrz7s

xoshrz7s11#

如果join对您不起作用,您可以尝试此解决方案。它用于在不使用外键+特定where条件时从t1中删除孤立记录。i、 它从表1中删除具有空字段“code”的记录,而在表2中没有按字段“name”匹配的记录。

delete table1 from table1 t1 
    where  t1.code = '' 
    and 0=(select count(t2.name) from table2 t2 where t2.name=t1.name);
68de4m5k

68de4m5k12#

另一种使用子选择进行删除的方法,比使用 IN 会是
WHERE EXISTS ```
DELETE FROM posts
WHERE EXISTS ( SELECT 1
FROM projects
WHERE projects.client_id = posts.client_id);

使用this而不是join的一个原因是 `DELETE` 与 `JOIN` 禁止使用 `LIMIT` . 如果希望在块中删除以便不产生完整的表锁,可以添加 `LIMIT` 用这个 `DELETE WHERE EXISTS` 方法。
5fjcxozz

5fjcxozz13#

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

从一个表中删除记录:

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

从两个表中删除记录:

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

iszxjhcz14#

或者同样的事情,使用稍微不同的语法(imo更友好):

DELETE FROM posts 
USING posts, projects 
WHERE projects.project_id = posts.project_id AND projects.client_id = :client_id;

顺便说一句,mysql使用连接几乎总是比子查询快得多。。。

相关问题