Mixing
how can I delete the entries which my mysql query finds?
SELECT u.name, u.age, u.sex
FROM User u
INNER JOIN (
SELECT age, sex, COUNT(*)
FROM User
GROUP BY age, sex
HAVING COUNT(*) > 1) temp
ON temp.age = u.age
AND temp.sex = u.sex
ORDER BY age, sex
this query finds all my duplicates in my database, but how can I delete the multiple ones?
mysql database duplicates
asked Nov 21 '18 at 16:14
Dieter BrowDieter Brow
43
add a comment |
SELECT u.name, u.age, u.sex
FROM User u
INNER JOIN (
SELECT age, sex, COUNT(*)
FROM User
GROUP BY age, sex
HAVING COUNT(*) > 1) temp
ON temp.age = u.age
AND temp.sex = u.sex
ORDER BY age, sex
this query finds all my duplicates in my database, but how can I delete the multiple ones?
mysql database duplicates
asked Nov 21 '18 at 16:14
Dieter BrowDieter Brow
43
Try searching for mysql delete duplicates. If you cannot find anything you can adapt add sample data as text to the question together with expected outcome.
– P.Salmon
Nov 21 '18 at 16:17
add a comment |
SELECT u.name, u.age, u.sex
FROM User u
INNER JOIN (
SELECT age, sex, COUNT(*)
FROM User
GROUP BY age, sex
HAVING COUNT(*) > 1) temp
ON temp.age = u.age
AND temp.sex = u.sex
ORDER BY age, sex
this query finds all my duplicates in my database, but how can I delete the multiple ones?
mysql database duplicates
asked Nov 21 '18 at 16:14
Dieter BrowDieter Brow
43
SELECT u.name, u.age, u.sex
FROM User u
INNER JOIN (
SELECT age, sex, COUNT(*)
FROM User
GROUP BY age, sex
HAVING COUNT(*) > 1) temp
ON temp.age = u.age
AND temp.sex = u.sex
ORDER BY age, sex
this query finds all my duplicates in my database, but how can I delete the multiple ones?
mysql database duplicates
mysql database duplicates
asked Nov 21 '18 at 16:14
Dieter BrowDieter Brow
43
asked Nov 21 '18 at 16:14
Dieter BrowDieter Brow
43
asked Nov 21 '18 at 16:14
Dieter BrowDieter Brow
43
asked Nov 21 '18 at 16:14
Dieter BrowDieter Brow
43
asked Nov 21 '18 at 16:14
Dieter BrowDieter Brow
43
43
Try searching for mysql delete duplicates. If you cannot find anything you can adapt add sample data as text to the question together with expected outcome.
– P.Salmon
Nov 21 '18 at 16:17
add a comment |
Try searching for mysql delete duplicates. If you cannot find anything you can adapt add sample data as text to the question together with expected outcome.
– P.Salmon
Nov 21 '18 at 16:17
Try searching for mysql delete duplicates. If you cannot find anything you can adapt add sample data as text to the question together with expected outcome.
– P.Salmon
Nov 21 '18 at 16:17
Try searching for mysql delete duplicates. If you cannot find anything you can adapt add sample data as text to the question together with expected outcome.
– P.Salmon
Nov 21 '18 at 16:17
add a comment |
1 Answer
1
active
oldest
votes
Select the ID of the row your are selecting only, then put DELETE that will delete all row with these selected ID.
DELETE FROM User u
WHERE u.ID IN
(
SELECT u.ID
FROM User u
INNER JOIN
(
SELECT u.ID
FROM User
GROUP BY age, sex
HAVING COUNT(*) > 1
) temp
ON temp.age = u.age
AND temp.sex = u.sex
ORDER BY age, sex
)
But I have to advise you that you should change your query as it will erase everything. Your select statement is the same than SELECT * FROM User as everything if inner joined. So I guess what you really want is:
DELETE FROM User u
WHERE u.ID IN
(
SELECT u.ID
FROM User
GROUP BY age, sex
HAVING COUNT(*) > 1
)
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
});
}
});
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%2f53416259%2fhow-can-i-delete-the-entries-which-my-mysql-query-finds%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
Select the ID of the row your are selecting only, then put DELETE that will delete all row with these selected ID.
DELETE FROM User u
WHERE u.ID IN
(
SELECT u.ID
FROM User u
INNER JOIN
(
SELECT u.ID
FROM User
GROUP BY age, sex
HAVING COUNT(*) > 1
) temp
ON temp.age = u.age
AND temp.sex = u.sex
ORDER BY age, sex
)
But I have to advise you that you should change your query as it will erase everything. Your select statement is the same than SELECT * FROM User as everything if inner joined. So I guess what you really want is:
DELETE FROM User u
WHERE u.ID IN
(
SELECT u.ID
FROM User
GROUP BY age, sex
HAVING COUNT(*) > 1
)
add a comment |
Select the ID of the row your are selecting only, then put DELETE that will delete all row with these selected ID.
DELETE FROM User u
WHERE u.ID IN
(
SELECT u.ID
FROM User u
INNER JOIN
(
SELECT u.ID
FROM User
GROUP BY age, sex
HAVING COUNT(*) > 1
) temp
ON temp.age = u.age
AND temp.sex = u.sex
ORDER BY age, sex
)
But I have to advise you that you should change your query as it will erase everything. Your select statement is the same than SELECT * FROM User as everything if inner joined. So I guess what you really want is:
DELETE FROM User u
WHERE u.ID IN
(
SELECT u.ID
FROM User
GROUP BY age, sex
HAVING COUNT(*) > 1
)
add a comment |
Select the ID of the row your are selecting only, then put DELETE that will delete all row with these selected ID.
DELETE FROM User u
WHERE u.ID IN
(
SELECT u.ID
FROM User u
INNER JOIN
(
SELECT u.ID
FROM User
GROUP BY age, sex
HAVING COUNT(*) > 1
) temp
ON temp.age = u.age
AND temp.sex = u.sex
ORDER BY age, sex
)
But I have to advise you that you should change your query as it will erase everything. Your select statement is the same than SELECT * FROM User as everything if inner joined. So I guess what you really want is:
DELETE FROM User u
WHERE u.ID IN
(
SELECT u.ID
FROM User
GROUP BY age, sex
HAVING COUNT(*) > 1
)
Select the ID of the row your are selecting only, then put DELETE that will delete all row with these selected ID.
DELETE FROM User u
WHERE u.ID IN
(
SELECT u.ID
FROM User u
INNER JOIN
(
SELECT u.ID
FROM User
GROUP BY age, sex
HAVING COUNT(*) > 1
) temp
ON temp.age = u.age
AND temp.sex = u.sex
ORDER BY age, sex
)
But I have to advise you that you should change your query as it will erase everything. Your select statement is the same than SELECT * FROM User as everything if inner joined. So I guess what you really want is:
DELETE FROM User u
WHERE u.ID IN
(
SELECT u.ID
FROM User
GROUP BY age, sex
HAVING COUNT(*) > 1
)
edited Nov 21 '18 at 16:32
answered Nov 21 '18 at 16:26
user10421833
add a comment |
add a comment |
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.
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%2f53416259%2fhow-can-i-delete-the-entries-which-my-mysql-query-finds%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
Try searching for mysql delete duplicates. If you cannot find anything you can adapt add sample data as text to the question together with expected outcome.
– P.Salmon
Nov 21 '18 at 16:17