How to remove duplicate users with firstname/lastname swapped in sql server
0
Consider I have a table like the one below :
Firstname : LastName
Ajay : Sharma
Sharma : Ajay
Gopi : Nair
Nair : Gopi
Vikram : Roy
Anil : Patel
Patel : Anil
I would like to get the output as
Firstname : LastName
Ajay : Sharma
Gopi : Nair
Vikram : Roy
Anil : Patel
sql duplicates
edited Nov 24 '18 at 13:25
Gordon Linoff
780k35310412
asked Nov 24 '18 at 13:13
user9489168user9489168
103
add a comment |
0
Consider I have a table like the one below :
Firstname : LastName
Ajay : Sharma
Sharma : Ajay
Gopi : Nair
Nair : Gopi
Vikram : Roy
Anil : Patel
Patel : Anil
I would like to get the output as
Firstname : LastName
Ajay : Sharma
Gopi : Nair
Vikram : Roy
Anil : Patel
sql duplicates
edited Nov 24 '18 at 13:25
Gordon Linoff
780k35310412
asked Nov 24 '18 at 13:13
user9489168user9489168
103
1
wich db you are using?
– scaisEdge
Nov 24 '18 at 13:20
add a comment |
0
0
0
Consider I have a table like the one below :
Firstname : LastName
Ajay : Sharma
Sharma : Ajay
Gopi : Nair
Nair : Gopi
Vikram : Roy
Anil : Patel
Patel : Anil
I would like to get the output as
Firstname : LastName
Ajay : Sharma
Gopi : Nair
Vikram : Roy
Anil : Patel
sql duplicates
edited Nov 24 '18 at 13:25
Gordon Linoff
780k35310412
asked Nov 24 '18 at 13:13
user9489168user9489168
103
Consider I have a table like the one below :
Firstname : LastName
Ajay : Sharma
Sharma : Ajay
Gopi : Nair
Nair : Gopi
Vikram : Roy
Anil : Patel
Patel : Anil
I would like to get the output as
Firstname : LastName
Ajay : Sharma
Gopi : Nair
Vikram : Roy
Anil : Patel
sql duplicates
sql duplicates
edited Nov 24 '18 at 13:25
Gordon Linoff
780k35310412
asked Nov 24 '18 at 13:13
user9489168user9489168
103
edited Nov 24 '18 at 13:25
Gordon Linoff
780k35310412
asked Nov 24 '18 at 13:13
user9489168user9489168
103
edited Nov 24 '18 at 13:25
Gordon Linoff
780k35310412
edited Nov 24 '18 at 13:25
Gordon Linoff
780k35310412
edited Nov 24 '18 at 13:25
Gordon Linoff
780k35310412
780k35310412
asked Nov 24 '18 at 13:13
user9489168user9489168
103
asked Nov 24 '18 at 13:13
user9489168user9489168
103
asked Nov 24 '18 at 13:13
user9489168user9489168
103
103
1
wich db you are using?
– scaisEdge
Nov 24 '18 at 13:20
add a comment |
1
wich db you are using?
– scaisEdge
Nov 24 '18 at 13:20
1
1
wich db you are using?
– scaisEdge
Nov 24 '18 at 13:20
wich db you are using?
– scaisEdge
Nov 24 '18 at 13:20
add a comment |
3 Answers
3
active
oldest
votes
0
Without a list of usual first names and/or last names, it is not possible to keep the one that "looks" like the first name. So, you have to resort to just keeping one of the pair.
I would go for:
select t.firstname, t.lastname
from t
where t.firstname <= t.lastname
union all
select t.lastname, t.firstname
from t
where t.firstname > t.lastname and
not exists (select 1
from t t2
where t2.firstname = t.lastname and
t2.lastname = t.firstname
);
This keeps all rows where firstname is less than or equal to lastname. It then keeps inverted pairs where the inversion does not exist.
If you actually want to delete the rows:
delete from t
where t.lastname > t.firstname and
exists (select 1
from t t2
where t2.firstname = t.lastname and
t2.lastname = t.firstname
);
answered Nov 24 '18 at 13:24
Gordon LinoffGordon Linoff
780k35310412
Hi Gordon, Thanks for the delete row query,It works!!Can you please explain your query?
– user9489168
Nov 24 '18 at 16:58
add a comment |
0
SELECT FIRSTNAME,LASTNAME FROM TABLE WHERE
LASTNAME, FIRSTNAME NOT IN (SELECT
FIRSTNAME,LASTNAME FROM TABLE) ;
answered Nov 24 '18 at 13:19
Himanshu AhujaHimanshu Ahuja
8492217
add a comment |
0
You could use a delete in join with the subquery that in UNION all return the duplicated
delete m
from my_table m
inner
(select t.firtname, t.lastname
from
(select firtname, lastname
from my_table
union all
select lastname, firstname
from my_table) t
group by
t.firtname, t.lastname
having
count(*) > 1) t2 on t2.lastname = m.firstname
and t2.firstname = m.lastname
edited Dec 15 '18 at 18:09
marc_s
579k12911181264
answered Nov 24 '18 at 13:19
scaisEdgescaisEdge
95.3k105071
I dont think this should be rather made complex like this
– Himanshu Ahuja
Nov 24 '18 at 13:25
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%2f53458506%2fhow-to-remove-duplicate-users-with-firstname-lastname-swapped-in-sql-server%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
3 Answers
3
active
oldest
votes
3 Answers
3
active
oldest
votes
active
oldest
votes
active
oldest
votes
0
Without a list of usual first names and/or last names, it is not possible to keep the one that "looks" like the first name. So, you have to resort to just keeping one of the pair.
I would go for:
select t.firstname, t.lastname
from t
where t.firstname <= t.lastname
union all
select t.lastname, t.firstname
from t
where t.firstname > t.lastname and
not exists (select 1
from t t2
where t2.firstname = t.lastname and
t2.lastname = t.firstname
);
This keeps all rows where firstname is less than or equal to lastname. It then keeps inverted pairs where the inversion does not exist.
If you actually want to delete the rows:
delete from t
where t.lastname > t.firstname and
exists (select 1
from t t2
where t2.firstname = t.lastname and
t2.lastname = t.firstname
);
answered Nov 24 '18 at 13:24
Gordon LinoffGordon Linoff
780k35310412
Hi Gordon, Thanks for the delete row query,It works!!Can you please explain your query?
– user9489168
Nov 24 '18 at 16:58
add a comment |
0
Without a list of usual first names and/or last names, it is not possible to keep the one that "looks" like the first name. So, you have to resort to just keeping one of the pair.
I would go for:
select t.firstname, t.lastname
from t
where t.firstname <= t.lastname
union all
select t.lastname, t.firstname
from t
where t.firstname > t.lastname and
not exists (select 1
from t t2
where t2.firstname = t.lastname and
t2.lastname = t.firstname
);
This keeps all rows where firstname is less than or equal to lastname. It then keeps inverted pairs where the inversion does not exist.
If you actually want to delete the rows:
delete from t
where t.lastname > t.firstname and
exists (select 1
from t t2
where t2.firstname = t.lastname and
t2.lastname = t.firstname
);
answered Nov 24 '18 at 13:24
Gordon LinoffGordon Linoff
780k35310412
Hi Gordon, Thanks for the delete row query,It works!!Can you please explain your query?
– user9489168
Nov 24 '18 at 16:58
add a comment |
0
0
0
Without a list of usual first names and/or last names, it is not possible to keep the one that "looks" like the first name. So, you have to resort to just keeping one of the pair.
I would go for:
select t.firstname, t.lastname
from t
where t.firstname <= t.lastname
union all
select t.lastname, t.firstname
from t
where t.firstname > t.lastname and
not exists (select 1
from t t2
where t2.firstname = t.lastname and
t2.lastname = t.firstname
);
This keeps all rows where firstname is less than or equal to lastname. It then keeps inverted pairs where the inversion does not exist.
If you actually want to delete the rows:
delete from t
where t.lastname > t.firstname and
exists (select 1
from t t2
where t2.firstname = t.lastname and
t2.lastname = t.firstname
);
answered Nov 24 '18 at 13:24
Gordon LinoffGordon Linoff
780k35310412
Without a list of usual first names and/or last names, it is not possible to keep the one that "looks" like the first name. So, you have to resort to just keeping one of the pair.
I would go for:
select t.firstname, t.lastname
from t
where t.firstname <= t.lastname
union all
select t.lastname, t.firstname
from t
where t.firstname > t.lastname and
not exists (select 1
from t t2
where t2.firstname = t.lastname and
t2.lastname = t.firstname
);
This keeps all rows where firstname is less than or equal to lastname. It then keeps inverted pairs where the inversion does not exist.
If you actually want to delete the rows:
delete from t
where t.lastname > t.firstname and
exists (select 1
from t t2
where t2.firstname = t.lastname and
t2.lastname = t.firstname
);
answered Nov 24 '18 at 13:24
Gordon LinoffGordon Linoff
780k35310412
edited Nov 25 '18 at 21:50
answered Nov 24 '18 at 13:24
Gordon LinoffGordon Linoff
780k35310412
answered Nov 24 '18 at 13:24
Gordon LinoffGordon Linoff
780k35310412
answered Nov 24 '18 at 13:24
Gordon LinoffGordon Linoff
780k35310412
780k35310412
Hi Gordon, Thanks for the delete row query,It works!!Can you please explain your query?
– user9489168
Nov 24 '18 at 16:58
add a comment |
Hi Gordon, Thanks for the delete row query,It works!!Can you please explain your query?
– user9489168
Nov 24 '18 at 16:58
Hi Gordon, Thanks for the delete row query,It works!!Can you please explain your query?
– user9489168
Nov 24 '18 at 16:58
Hi Gordon, Thanks for the delete row query,It works!!Can you please explain your query?
– user9489168
Nov 24 '18 at 16:58
add a comment |
0
SELECT FIRSTNAME,LASTNAME FROM TABLE WHERE
LASTNAME, FIRSTNAME NOT IN (SELECT
FIRSTNAME,LASTNAME FROM TABLE) ;
answered Nov 24 '18 at 13:19
Himanshu AhujaHimanshu Ahuja
8492217
add a comment |
0
SELECT FIRSTNAME,LASTNAME FROM TABLE WHERE
LASTNAME, FIRSTNAME NOT IN (SELECT
FIRSTNAME,LASTNAME FROM TABLE) ;
answered Nov 24 '18 at 13:19
Himanshu AhujaHimanshu Ahuja
8492217
add a comment |
0
0
0
SELECT FIRSTNAME,LASTNAME FROM TABLE WHERE
LASTNAME, FIRSTNAME NOT IN (SELECT
FIRSTNAME,LASTNAME FROM TABLE) ;
answered Nov 24 '18 at 13:19
Himanshu AhujaHimanshu Ahuja
8492217
SELECT FIRSTNAME,LASTNAME FROM TABLE WHERE
LASTNAME, FIRSTNAME NOT IN (SELECT
FIRSTNAME,LASTNAME FROM TABLE) ;
answered Nov 24 '18 at 13:19
Himanshu AhujaHimanshu Ahuja
8492217
answered Nov 24 '18 at 13:19
Himanshu AhujaHimanshu Ahuja
8492217
answered Nov 24 '18 at 13:19
Himanshu AhujaHimanshu Ahuja
8492217
answered Nov 24 '18 at 13:19
Himanshu AhujaHimanshu Ahuja
8492217
8492217
add a comment |
add a comment |
0
You could use a delete in join with the subquery that in UNION all return the duplicated
delete m
from my_table m
inner
(select t.firtname, t.lastname
from
(select firtname, lastname
from my_table
union all
select lastname, firstname
from my_table) t
group by
t.firtname, t.lastname
having
count(*) > 1) t2 on t2.lastname = m.firstname
and t2.firstname = m.lastname
edited Dec 15 '18 at 18:09
marc_s
579k12911181264
answered Nov 24 '18 at 13:19
scaisEdgescaisEdge
95.3k105071
I dont think this should be rather made complex like this
– Himanshu Ahuja
Nov 24 '18 at 13:25
add a comment |
0
You could use a delete in join with the subquery that in UNION all return the duplicated
delete m
from my_table m
inner
(select t.firtname, t.lastname
from
(select firtname, lastname
from my_table
union all
select lastname, firstname
from my_table) t
group by
t.firtname, t.lastname
having
count(*) > 1) t2 on t2.lastname = m.firstname
and t2.firstname = m.lastname
edited Dec 15 '18 at 18:09
marc_s
579k12911181264
answered Nov 24 '18 at 13:19
scaisEdgescaisEdge
95.3k105071
I dont think this should be rather made complex like this
– Himanshu Ahuja
Nov 24 '18 at 13:25
add a comment |
0
0
0
You could use a delete in join with the subquery that in UNION all return the duplicated
delete m
from my_table m
inner
(select t.firtname, t.lastname
from
(select firtname, lastname
from my_table
union all
select lastname, firstname
from my_table) t
group by
t.firtname, t.lastname
having
count(*) > 1) t2 on t2.lastname = m.firstname
and t2.firstname = m.lastname
edited Dec 15 '18 at 18:09
marc_s
579k12911181264
answered Nov 24 '18 at 13:19
scaisEdgescaisEdge
95.3k105071
You could use a delete in join with the subquery that in UNION all return the duplicated
delete m
from my_table m
inner
(select t.firtname, t.lastname
from
(select firtname, lastname
from my_table
union all
select lastname, firstname
from my_table) t
group by
t.firtname, t.lastname
having
count(*) > 1) t2 on t2.lastname = m.firstname
and t2.firstname = m.lastname
edited Dec 15 '18 at 18:09
marc_s
579k12911181264
answered Nov 24 '18 at 13:19
scaisEdgescaisEdge
95.3k105071
edited Dec 15 '18 at 18:09
marc_s
579k12911181264
edited Dec 15 '18 at 18:09
marc_s
579k12911181264
edited Dec 15 '18 at 18:09
marc_s
579k12911181264
579k12911181264
answered Nov 24 '18 at 13:19
scaisEdgescaisEdge
95.3k105071
answered Nov 24 '18 at 13:19
scaisEdgescaisEdge
95.3k105071
answered Nov 24 '18 at 13:19
scaisEdgescaisEdge
95.3k105071
95.3k105071
I dont think this should be rather made complex like this
– Himanshu Ahuja
Nov 24 '18 at 13:25
add a comment |
I dont think this should be rather made complex like this
– Himanshu Ahuja
Nov 24 '18 at 13:25
I dont think this should be rather made complex like this
– Himanshu Ahuja
Nov 24 '18 at 13:25
I dont think this should be rather made complex like this
– Himanshu Ahuja
Nov 24 '18 at 13:25
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%2f53458506%2fhow-to-remove-duplicate-users-with-firstname-lastname-swapped-in-sql-server%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
1
wich db you are using?
– scaisEdge
Nov 24 '18 at 13:20