How to remove duplicate rows in a SQL Server table [duplicate]
This question already has an answer here:
How can I remove duplicate rows?
37 answers
I have a SQL Server table with ~100 columns including the columns Id and CreationDate. Due to a bad constraint in its initial design, there are now many duplicate rows (i.e. rows whose values are identical across ALL columns).
Can you suggest a script to remove those duplicate rows?
Also, what would be a script to select all distinct Ids with the latest CreationDate?
Thanks
sql
sql-server edited Dec 27 '18 at 15:23
marc_s
570k12811031251
asked Dec 27 '18 at 14:58
Hanch
101
marked as duplicate by Tim Biegeleisen sql
Users with the sql badge can single-handedly close sql questions as duplicates and reopen them as needed.
StackExchange.ready(function() {
if (StackExchange.options.isMobile) return;
$('.dupe-hammer-message-hover:not(.hover-bound)').each(function() {
var $hover = $(this).addClass('hover-bound'),
$msg = $hover.siblings('.dupe-hammer-message');
$hover.hover(
function() {
$hover.showInfoMessage('', {
messageElement: $msg.clone().show(),
transient: false,
position: { my: 'bottom left', at: 'top center', offsetTop: -7 },
dismissable: false,
relativeToBody: true
});
},
function() {
StackExchange.helpers.removeMessages();
}
);
});
});
Dec 27 '18 at 15:07
This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.
add a comment |
This question already has an answer here:
How can I remove duplicate rows?
37 answers
I have a SQL Server table with ~100 columns including the columns Id and CreationDate. Due to a bad constraint in its initial design, there are now many duplicate rows (i.e. rows whose values are identical across ALL columns).
Can you suggest a script to remove those duplicate rows?
Also, what would be a script to select all distinct Ids with the latest CreationDate?
Thanks
sql
sql-server edited Dec 27 '18 at 15:23
marc_s
570k12811031251
asked Dec 27 '18 at 14:58
Hanch
101
marked as duplicate by Tim Biegeleisen sql
Users with the sql badge can single-handedly close sql questions as duplicates and reopen them as needed.
StackExchange.ready(function() {
if (StackExchange.options.isMobile) return;
$('.dupe-hammer-message-hover:not(.hover-bound)').each(function() {
var $hover = $(this).addClass('hover-bound'),
$msg = $hover.siblings('.dupe-hammer-message');
$hover.hover(
function() {
$hover.showInfoMessage('', {
messageElement: $msg.clone().show(),
transient: false,
position: { my: 'bottom left', at: 'top center', offsetTop: -7 },
dismissable: false,
relativeToBody: true
});
},
function() {
StackExchange.helpers.removeMessages();
}
);
});
});
Dec 27 '18 at 15:07
This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.
add a comment |
This question already has an answer here:
How can I remove duplicate rows?
37 answers
I have a SQL Server table with ~100 columns including the columns Id and CreationDate. Due to a bad constraint in its initial design, there are now many duplicate rows (i.e. rows whose values are identical across ALL columns).
Can you suggest a script to remove those duplicate rows?
Also, what would be a script to select all distinct Ids with the latest CreationDate?
Thanks
sql
sql-server edited Dec 27 '18 at 15:23
marc_s
570k12811031251
asked Dec 27 '18 at 14:58
Hanch
101
This question already has an answer here:
How can I remove duplicate rows?
37 answers
I have a SQL Server table with ~100 columns including the columns Id and CreationDate. Due to a bad constraint in its initial design, there are now many duplicate rows (i.e. rows whose values are identical across ALL columns).
Can you suggest a script to remove those duplicate rows?
Also, what would be a script to select all distinct Ids with the latest CreationDate?
Thanks
This question already has an answer here:
How can I remove duplicate rows?
37 answers
sql
sql-server sql
sql-server edited Dec 27 '18 at 15:23
marc_s
570k12811031251
asked Dec 27 '18 at 14:58
Hanch
101
edited Dec 27 '18 at 15:23
marc_s
570k12811031251
asked Dec 27 '18 at 14:58
Hanch
101
edited Dec 27 '18 at 15:23
marc_s
570k12811031251
edited Dec 27 '18 at 15:23
marc_s
570k12811031251
edited Dec 27 '18 at 15:23
marc_s
570k12811031251
570k12811031251
asked Dec 27 '18 at 14:58
Hanch
101
asked Dec 27 '18 at 14:58
Hanch
101
asked Dec 27 '18 at 14:58
Hanch
101
101
marked as duplicate by Tim Biegeleisen sql
Users with the sql badge can single-handedly close sql questions as duplicates and reopen them as needed.
StackExchange.ready(function() {
if (StackExchange.options.isMobile) return;
$('.dupe-hammer-message-hover:not(.hover-bound)').each(function() {
var $hover = $(this).addClass('hover-bound'),
$msg = $hover.siblings('.dupe-hammer-message');
$hover.hover(
function() {
$hover.showInfoMessage('', {
messageElement: $msg.clone().show(),
transient: false,
position: { my: 'bottom left', at: 'top center', offsetTop: -7 },
dismissable: false,
relativeToBody: true
});
},
function() {
StackExchange.helpers.removeMessages();
}
);
});
});
Dec 27 '18 at 15:07
This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.
marked as duplicate by Tim Biegeleisen sql
Users with the sql badge can single-handedly close sql questions as duplicates and reopen them as needed.
StackExchange.ready(function() {
if (StackExchange.options.isMobile) return;
$('.dupe-hammer-message-hover:not(.hover-bound)').each(function() {
var $hover = $(this).addClass('hover-bound'),
$msg = $hover.siblings('.dupe-hammer-message');
$hover.hover(
function() {
$hover.showInfoMessage('', {
messageElement: $msg.clone().show(),
transient: false,
position: { my: 'bottom left', at: 'top center', offsetTop: -7 },
dismissable: false,
relativeToBody: true
});
},
function() {
StackExchange.helpers.removeMessages();
}
);
});
});
Dec 27 '18 at 15:07
This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
You can use the following script to remove duplicate rows from a Microsoft SQL Server table:
SELECT DISTINCT *
INTO duplicate_table
FROM original_table
GROUP BY key_value
HAVING COUNT(key_value) > 1
DELETE original_table
WHERE key_value
IN (SELECT key_value
FROM duplicate_table)
INSERT original_table
SELECT *
FROM duplicate_table
DROP TABLE duplicate_table
When this script is executed, it follows these steps:
It moves one instance of any duplicate row in the original table to a duplicate table.
It deletes all rows from the original table that also reside in the duplicate table.
It moves the rows in the duplicate table back into the original table.
It drops the duplicate table.
edited Dec 27 '18 at 16:46
marc_s
570k12811031251
answered Dec 27 '18 at 16:25
yaghob abbasi
136
add a comment |
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
You can use the following script to remove duplicate rows from a Microsoft SQL Server table:
SELECT DISTINCT *
INTO duplicate_table
FROM original_table
GROUP BY key_value
HAVING COUNT(key_value) > 1
DELETE original_table
WHERE key_value
IN (SELECT key_value
FROM duplicate_table)
INSERT original_table
SELECT *
FROM duplicate_table
DROP TABLE duplicate_table
When this script is executed, it follows these steps:
It moves one instance of any duplicate row in the original table to a duplicate table.
It deletes all rows from the original table that also reside in the duplicate table.
It moves the rows in the duplicate table back into the original table.
It drops the duplicate table.
edited Dec 27 '18 at 16:46
marc_s
570k12811031251
answered Dec 27 '18 at 16:25
yaghob abbasi
136
add a comment |
You can use the following script to remove duplicate rows from a Microsoft SQL Server table:
SELECT DISTINCT *
INTO duplicate_table
FROM original_table
GROUP BY key_value
HAVING COUNT(key_value) > 1
DELETE original_table
WHERE key_value
IN (SELECT key_value
FROM duplicate_table)
INSERT original_table
SELECT *
FROM duplicate_table
DROP TABLE duplicate_table
When this script is executed, it follows these steps:
It moves one instance of any duplicate row in the original table to a duplicate table.
It deletes all rows from the original table that also reside in the duplicate table.
It moves the rows in the duplicate table back into the original table.
It drops the duplicate table.
edited Dec 27 '18 at 16:46
marc_s
570k12811031251
answered Dec 27 '18 at 16:25
yaghob abbasi
136
add a comment |
You can use the following script to remove duplicate rows from a Microsoft SQL Server table:
SELECT DISTINCT *
INTO duplicate_table
FROM original_table
GROUP BY key_value
HAVING COUNT(key_value) > 1
DELETE original_table
WHERE key_value
IN (SELECT key_value
FROM duplicate_table)
INSERT original_table
SELECT *
FROM duplicate_table
DROP TABLE duplicate_table
When this script is executed, it follows these steps:
It moves one instance of any duplicate row in the original table to a duplicate table.
It deletes all rows from the original table that also reside in the duplicate table.
It moves the rows in the duplicate table back into the original table.
It drops the duplicate table.
edited Dec 27 '18 at 16:46
marc_s
570k12811031251
answered Dec 27 '18 at 16:25
yaghob abbasi
136
You can use the following script to remove duplicate rows from a Microsoft SQL Server table:
SELECT DISTINCT *
INTO duplicate_table
FROM original_table
GROUP BY key_value
HAVING COUNT(key_value) > 1
DELETE original_table
WHERE key_value
IN (SELECT key_value
FROM duplicate_table)
INSERT original_table
SELECT *
FROM duplicate_table
DROP TABLE duplicate_table
When this script is executed, it follows these steps:
It moves one instance of any duplicate row in the original table to a duplicate table.
It deletes all rows from the original table that also reside in the duplicate table.
It moves the rows in the duplicate table back into the original table.
It drops the duplicate table.
edited Dec 27 '18 at 16:46
marc_s
570k12811031251
answered Dec 27 '18 at 16:25
yaghob abbasi
136
edited Dec 27 '18 at 16:46
marc_s
570k12811031251
edited Dec 27 '18 at 16:46
marc_s
570k12811031251
edited Dec 27 '18 at 16:46
marc_s
570k12811031251
570k12811031251
answered Dec 27 '18 at 16:25
yaghob abbasi
136
answered Dec 27 '18 at 16:25
yaghob abbasi
136
answered Dec 27 '18 at 16:25
yaghob abbasi
136
136
add a comment |
add a comment |
