删除加入MySQL

13 回答

• 1116

  试试这个，

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

  回复于 2024-05-18T00:36:56+08:00
• 66

  或者同样的事情，稍微不同（IMO友好）的语法：

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

  顺便说一句，使用连接的mysql几乎总是比子查询更快...

  回复于 2024-05-18T00:36:56+08:00
• 7

  MySQL使用JOIN删除记录

  通常在SELECT语句中使用INNER JOIN从表中选择具有其他表中相应记录的记录 . 我们还可以使用带有DELETE语句的INNER JOIN子句来删除表中的记录以及其他表中的相应记录，例如，要删除满足特定条件的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中的哪些记录 .

  回复于 2024-05-18T00:36:56+08:00
• 4

  尝试如下：

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

  回复于 2024-05-18T00:36:56+08:00
• 7

  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:

  要从连接结果中删除多个表中的条目，您需要在 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
  

  回复于 2024-05-18T00:36:56+08:00
• 1

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

  回复于 2024-05-18T00:36:56+08:00
• 46

  我更习惯于子查询解决方案，但我还没有在MySQL中尝试过：

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

  回复于 2024-05-18T00:36:56+08:00
• 11

  如果加入对您不起作用，您可以尝试此解决方案 . 它用于在不使用特定条件的外键时从t1删除孤立记录 . 即它删除table1中的记录，这些记录具有空字段“code”，并且在table2中没有记录，通过字段“name”进行匹配 .

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

  回复于 2024-05-18T00:36:56+08:00
• 38

  由于您要选择多个表，因此要删除的表不再明确 . 您需要选择：

  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 don't work for multi-table deletes .

  另请注意，如果声明表的别名，则在引用表时必须使用别名：

  delete p from posts as p inner join [...]
  

  ---

  来自Carpetsmoker等的捐款

  回复于 2024-05-18T00:36:56+08:00
• 0

  您也可以像这样使用ALIAS它只是在我的数据库中使用它！ t是需要删除的表格！

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

  回复于 2024-05-18T00:36:56+08:00
• -3

  • 请注意，您不能在需要删除的表上使用别名

  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
  

  回复于 2024-05-18T00:36:56+08:00
• 24

  您只需要指定要删除 posts 表中的条目：

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

  编辑：有关更多信息，请参阅this alternative answer

  回复于 2024-05-18T00:36:56+08:00
• 4

  使用比使用 IN 更好的子选择删除的另一种方法是 WHERE EXISTS

  DELETE  FROM posts
  WHERE   EXISTS ( SELECT  1 
                   FROM    projects
                   WHERE   projects.client_id = posts.client_id);
  

  使用此而不是连接的一个原因是 DELETE 与 JOIN 禁止使用 LIMIT . 如果您希望在块中删除以便不生成完整的表锁，可以添加 LIMIT 使用此 DELETE WHERE EXISTS 方法 .

  回复于 2024-05-18T00:36:56+08:00