CODEWITHSUNDEEP
mysql
Nabil Sham
Nabil Sham

Reputation: 2345

MySql delete from select with multiple column?

The following 

delete from child N
where parent_id in
(select parent_id , count(*) c
from parent
group by other_attribute  having c > 1 )

understandably returns 

Operand should contain 1 column(s)

How do you fix it ?

Upvotes: 0

Views: 1058

Answers (2)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521103

You don't need to select the count in the subquery. Just use the count explicitly in the HAVING clause:

delete from child N
where parent_id in
(
    select parent_id
    from parent
    group by other_attribute
    having count(*) > 1
)

Upvotes: 1

Rahul
Rahul

Reputation: 77866

Modify your query to be

delete from child 
where parent_id in (
select parent_id 
 from parent   
group by other_attribute  
having count(*) > 1 )

(OR)

Make it a JOIN query saying

delete child 
from child
join (    
select parent_id 
from parent   
group by other_attribute  
having count(*) > 1 ) xxx on child.parent_id = xxx.parent_id;

Upvotes: 0

Related Questions