Mysql Delete all matching values if another column satisfies a condition
1
I have the below table order_details:
|---------------------|------------------|------------------|
| order_id | sap_number | product_name |
|---------------------|------------------|------------------|
| 1 | 123 | earphones |
|---------------------|------------------|------------------|
| 2 | 123 | battery |
|---------------------|------------------|------------------|
| 3 | 456 | charger |
|---------------------|------------------|------------------|
| 4 | 789 | phone |
|---------------------|------------------|------------------|
I want to delete all those records having same sap_number if product is battery. In this case I want records with order_id 1 and 2 to be deleted.
I tried this:
delete from order_details
where order_id in (
select order_id
from (
select order_id
from order_details
group by sap_number
having product = 'battery'
) t
)
but it deletes only the second record and not the first one.
Thanks.
mysql
asked Nov 15 '18 at 6:28
Trayi ShahTrayi Shah
133
add a comment |
1
I have the below table order_details:
|---------------------|------------------|------------------|
| order_id | sap_number | product_name |
|---------------------|------------------|------------------|
| 1 | 123 | earphones |
|---------------------|------------------|------------------|
| 2 | 123 | battery |
|---------------------|------------------|------------------|
| 3 | 456 | charger |
|---------------------|------------------|------------------|
| 4 | 789 | phone |
|---------------------|------------------|------------------|
I want to delete all those records having same sap_number if product is battery. In this case I want records with order_id 1 and 2 to be deleted.
I tried this:
delete from order_details
where order_id in (
select order_id
from (
select order_id
from order_details
group by sap_number
having product = 'battery'
) t
)
but it deletes only the second record and not the first one.
Thanks.
mysql
asked Nov 15 '18 at 6:28
Trayi ShahTrayi Shah
133
add a comment |
1
1
1
I have the below table order_details:
|---------------------|------------------|------------------|
| order_id | sap_number | product_name |
|---------------------|------------------|------------------|
| 1 | 123 | earphones |
|---------------------|------------------|------------------|
| 2 | 123 | battery |
|---------------------|------------------|------------------|
| 3 | 456 | charger |
|---------------------|------------------|------------------|
| 4 | 789 | phone |
|---------------------|------------------|------------------|
I want to delete all those records having same sap_number if product is battery. In this case I want records with order_id 1 and 2 to be deleted.
I tried this:
delete from order_details
where order_id in (
select order_id
from (
select order_id
from order_details
group by sap_number
having product = 'battery'
) t
)
but it deletes only the second record and not the first one.
Thanks.
mysql
asked Nov 15 '18 at 6:28
Trayi ShahTrayi Shah
133
I have the below table order_details:
|---------------------|------------------|------------------|
| order_id | sap_number | product_name |
|---------------------|------------------|------------------|
| 1 | 123 | earphones |
|---------------------|------------------|------------------|
| 2 | 123 | battery |
|---------------------|------------------|------------------|
| 3 | 456 | charger |
|---------------------|------------------|------------------|
| 4 | 789 | phone |
|---------------------|------------------|------------------|
I want to delete all those records having same sap_number if product is battery. In this case I want records with order_id 1 and 2 to be deleted.
I tried this:
delete from order_details
where order_id in (
select order_id
from (
select order_id
from order_details
group by sap_number
having product = 'battery'
) t
)
but it deletes only the second record and not the first one.
Thanks.
mysql
mysql
asked Nov 15 '18 at 6:28
Trayi ShahTrayi Shah
133
asked Nov 15 '18 at 6:28
Trayi ShahTrayi Shah
133
asked Nov 15 '18 at 6:28
Trayi ShahTrayi Shah
133
asked Nov 15 '18 at 6:28
Trayi ShahTrayi Shah
133
asked Nov 15 '18 at 6:28
Trayi ShahTrayi Shah
133
133
add a comment |
add a comment |
2 Answers
2
active
oldest
votes
0
Try this
delete from order_details where sap_number IN (select sap_number from (select * from order_details having product_name = 'battery') t);
answered Nov 15 '18 at 6:52
Prashant Deshmukh.....Prashant Deshmukh.....
68658
add a comment |
2
You can try below
delete from order_details
where sap_number in (
select sap_number
from order_details
where product_name= 'battery'
)
answered Nov 15 '18 at 6:33
fa06fa06
15k2917
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%2f53313621%2fmysql-delete-all-matching-values-if-another-column-satisfies-a-condition%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
0
Try this
delete from order_details where sap_number IN (select sap_number from (select * from order_details having product_name = 'battery') t);
answered Nov 15 '18 at 6:52
Prashant Deshmukh.....Prashant Deshmukh.....
68658
add a comment |
0
Try this
delete from order_details where sap_number IN (select sap_number from (select * from order_details having product_name = 'battery') t);
answered Nov 15 '18 at 6:52
Prashant Deshmukh.....Prashant Deshmukh.....
68658
add a comment |
0
0
0
Try this
delete from order_details where sap_number IN (select sap_number from (select * from order_details having product_name = 'battery') t);
answered Nov 15 '18 at 6:52
Prashant Deshmukh.....Prashant Deshmukh.....
68658
Try this
delete from order_details where sap_number IN (select sap_number from (select * from order_details having product_name = 'battery') t);
answered Nov 15 '18 at 6:52
Prashant Deshmukh.....Prashant Deshmukh.....
68658
answered Nov 15 '18 at 6:52
Prashant Deshmukh.....Prashant Deshmukh.....
68658
answered Nov 15 '18 at 6:52
Prashant Deshmukh.....Prashant Deshmukh.....
68658
answered Nov 15 '18 at 6:52
Prashant Deshmukh.....Prashant Deshmukh.....
68658
68658
add a comment |
add a comment |
2
You can try below
delete from order_details
where sap_number in (
select sap_number
from order_details
where product_name= 'battery'
)
answered Nov 15 '18 at 6:33
fa06fa06
15k2917
add a comment |
2
You can try below
delete from order_details
where sap_number in (
select sap_number
from order_details
where product_name= 'battery'
)
answered Nov 15 '18 at 6:33
fa06fa06
15k2917
add a comment |
2
2
2
You can try below
delete from order_details
where sap_number in (
select sap_number
from order_details
where product_name= 'battery'
)
answered Nov 15 '18 at 6:33
fa06fa06
15k2917
You can try below
delete from order_details
where sap_number in (
select sap_number
from order_details
where product_name= 'battery'
)
answered Nov 15 '18 at 6:33
fa06fa06
15k2917
edited Nov 15 '18 at 6:58
answered Nov 15 '18 at 6:33
fa06fa06
15k2917
answered Nov 15 '18 at 6:33
fa06fa06
15k2917
answered Nov 15 '18 at 6:33
fa06fa06
15k2917
15k2917
add a comment |
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%2f53313621%2fmysql-delete-all-matching-values-if-another-column-satisfies-a-condition%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
