DELETE query with WHERE EXISTS in MySQL [duplicate]

Question

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

wogsland · Accepted Answer · 2016-08-03 22:15:32Z

2

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

edited Aug 3, 2016 at 22:15

wogsland

9,53121 gold badges63 silver badges99 bronze badges

answered Aug 3, 2016 at 22:14

Sean Han

363 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

DELETE query with WHERE EXISTS in MySQL [duplicate]

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related