How to delete child records which is having count more than ten for a parent and I should retain parent...
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}
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.
sql phoenix
asked Nov 26 '18 at 17:36
Vinay PrasadVinay Prasad
12
add a comment |
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.
sql phoenix
asked Nov 26 '18 at 17:36
Vinay PrasadVinay Prasad
12
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 Ahuja
Nov 26 '18 at 18:13
Check this: stackoverflow.com/questions/6296102/mysql-delete-with-group-by
– TeeKea
Nov 26 '18 at 18:15
add a comment |
0
0
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.
sql phoenix
asked Nov 26 '18 at 17:36
Vinay PrasadVinay Prasad
12
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.
sql phoenix
sql phoenix
asked Nov 26 '18 at 17:36
Vinay PrasadVinay Prasad
12
asked Nov 26 '18 at 17:36
Vinay PrasadVinay Prasad
12
edited Nov 27 '18 at 5:17
Vinay Prasad
asked Nov 26 '18 at 17:36
Vinay PrasadVinay Prasad
12
asked Nov 26 '18 at 17:36
Vinay PrasadVinay Prasad
12
asked Nov 26 '18 at 17:36
Vinay PrasadVinay Prasad
12
12
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 Ahuja
Nov 26 '18 at 18:13
Check this: stackoverflow.com/questions/6296102/mysql-delete-with-group-by
– TeeKea
Nov 26 '18 at 18:15
add a comment |
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 Ahuja
Nov 26 '18 at 18:13
Check this: stackoverflow.com/questions/6296102/mysql-delete-with-group-by
– TeeKea
Nov 26 '18 at 18:15
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 Ahuja
Nov 26 '18 at 18:13
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 Ahuja
Nov 26 '18 at 18:13
Check this: stackoverflow.com/questions/6296102/mysql-delete-with-group-by
– TeeKea
Nov 26 '18 at 18:15
Check this: stackoverflow.com/questions/6296102/mysql-delete-with-group-by
– TeeKea
Nov 26 '18 at 18:15
add a comment |
1 Answer
1
active
oldest
votes
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
answered Nov 26 '18 at 18:18
Himanshu AhujaHimanshu Ahuja
9952219
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 7:24
check now does it work now
– Himanshu Ahuja
Nov 27 '18 at 13:25
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 Ahuja
Nov 27 '18 at 13:40
Create a trigger/procedure to do some operation on some value or your requirement
– Himanshu Ahuja
Dec 3 '18 at 18:30
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53486315%2fhow-to-delete-child-records-which-is-having-count-more-than-ten-for-a-parent-and%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
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
answered Nov 26 '18 at 18:18
Himanshu AhujaHimanshu Ahuja
9952219
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 7:24
check now does it work now
– Himanshu Ahuja
Nov 27 '18 at 13:25
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 Ahuja
Nov 27 '18 at 13:40
Create a trigger/procedure to do some operation on some value or your requirement
– Himanshu Ahuja
Dec 3 '18 at 18:30
add a comment |
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
answered Nov 26 '18 at 18:18
Himanshu AhujaHimanshu Ahuja
9952219
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 7:24
check now does it work now
– Himanshu Ahuja
Nov 27 '18 at 13:25
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 Ahuja
Nov 27 '18 at 13:40
Create a trigger/procedure to do some operation on some value or your requirement
– Himanshu Ahuja
Dec 3 '18 at 18:30
add a comment |
0
0
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
answered Nov 26 '18 at 18:18
Himanshu AhujaHimanshu Ahuja
9952219
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
answered Nov 26 '18 at 18:18
Himanshu AhujaHimanshu Ahuja
9952219
edited Nov 27 '18 at 13:37
answered Nov 26 '18 at 18:18
Himanshu AhujaHimanshu Ahuja
9952219
answered Nov 26 '18 at 18:18
Himanshu AhujaHimanshu Ahuja
9952219
answered Nov 26 '18 at 18:18
Himanshu AhujaHimanshu Ahuja
9952219
9952219
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 7:24
check now does it work now
– Himanshu Ahuja
Nov 27 '18 at 13:25
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 Ahuja
Nov 27 '18 at 13:40
Create a trigger/procedure to do some operation on some value or your requirement
– Himanshu Ahuja
Dec 3 '18 at 18:30
add a comment |
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 7:24
check now does it work now
– Himanshu Ahuja
Nov 27 '18 at 13:25
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 Ahuja
Nov 27 '18 at 13:40
Create a trigger/procedure to do some operation on some value or your requirement
– Himanshu Ahuja
Dec 3 '18 at 18:30
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 7:24
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 7:24
check now does it work now
– Himanshu Ahuja
Nov 27 '18 at 13:25
check now does it work now
– Himanshu Ahuja
Nov 27 '18 at 13:25
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 Ahuja
Nov 27 '18 at 13:40
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 Ahuja
Nov 27 '18 at 13:40
Create a trigger/procedure to do some operation on some value or your requirement
– Himanshu Ahuja
Dec 3 '18 at 18:30
Create a trigger/procedure to do some operation on some value or your requirement
– Himanshu Ahuja
Dec 3 '18 at 18:30
add a comment |
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53486315%2fhow-to-delete-child-records-which-is-having-count-more-than-ten-for-a-parent-and%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
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 Ahuja
Nov 26 '18 at 18:13
Check this: stackoverflow.com/questions/6296102/mysql-delete-with-group-by
– TeeKea
Nov 26 '18 at 18:15