MySql delete from select with multiple column?

Question

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 ?

Barmar · Accepted Answer · 2016-06-04 12:42:55Z

1

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
)

edited Jun 4, 2016 at 12:42

Barmar

789k57 gold badges555 silver badges669 bronze badges

answered Jun 4, 2016 at 12:33

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

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

Comments

Rahul · Accepted Answer · 2016-06-04 12:30:55Z

0

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;

answered Jun 4, 2016 at 12:30

Rahul

78.1k14 gold badges80 silver badges132 bronze badges

Collectives™ on Stack Overflow

MySql delete from select with multiple column?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related