0

I have a parent and child information in same table now I have to delete the child records which is having count more than ten for a single parent.

I was able to get count of child for each parent but I was not able delete only the child records having count more than ten for a parent and I should retain parent record and only delete child records

select parent,count(child) from table group by parent having count(child >10);

It gives results:

Parent. |  Count(child)

1053.   |    15
3052.   |    30
3467.   |    45

Now I have to delete only the count of child records more than 10 and I tried different ways but didn't help me.Any help is appreciated.

Vinay Prasad
  • 3
  • 4
  • `delete from table where parent in (Select parent from (Select parent,count(child) from table group by parent having count(child)>10))` check if this works – Himanshu Nov 26 '18 at 18:13
  • Check this: https://stackoverflow.com/questions/6296102/mysql-delete-with-group-by – Taher A. Ghaleb Nov 26 '18 at 18:15

1 Answers1

0
   UPDATE TABLE SET CHILD =NULL WHERE PARENT IN (SELECT 
  PARENT FROM(SELECT 
  PARENT,COUNT(CHILD) FROM TABLE GROUP BY 
  PARENT HAVING COUNT(CHILD)>10));

This will first get the parent with count as greater than 10 then delete that parent child records from the table

Himanshu
  • 3,830
  • 2
  • 10
  • 29
  • Here want to retain parent records and I only have delete the child records.but as you mentioned above it is deleting child and as well parent.but I want only to delete child records. – Vinay Prasad Nov 27 '18 at 07:24
  • you cant delete seperately column records here in case child as it is dependent on parent record just like how can you delete one column record in a row where other columns records are there for instance parent(1),child(1) how can you delete child only for that you need to delete full row or you can update that record – Himanshu Nov 27 '18 at 13:40
  • Create a trigger/procedure to do some operation on some value or your requirement – Himanshu Dec 03 '18 at 18:30