在 MySQL 中使用 WHERE EXISTS 删除查询 [英] DELETE query with WHERE EXISTS in MySQL

问题描述

我正在使用 MySql 中的一个表执行带有where exists"的查询.它适用于 SELECT *，但当我尝试执行 DELETE 而不是 SELECT * 时失败.

I am executing the query with "where exists" using one table in MySql. It works fine with SELECT *, but fails when I try to do DELETE instead of SELECT *.

如何使用删除执行相同的查询?非常感谢！

How to perform the same query with delete? Many thanks in advance!

select * from MyTable t1 where exists ( 
select * from MyTable t2 where t1.user_id = t2.user_id 
and t1.object_id <> t2.object_id and t2.role = "ADMIN")
and role = "ORG_MANAGER" 
and object_type = "type_b";

推荐答案

delete from MyTable t1 
where user_id in (
  select user_id 
  from MyTable t1 
  where exists ( 
    select * from MyTable t2 
    where t1.user_id = t2.user_id 
    and t1.object_id <> t2.object_id 
    and t2.role = "ADMIN")
  and role = "ORG_MANAGER" 
  and object_type = "type_b";
)

这篇关于在 MySQL 中使用 WHERE EXISTS 删除查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！