Eliminar registros duplicados en SQL Server?
Considere una columna llamada EmployeeName
tabla Employee
. El objetivo es eliminar registros repetidos, basados en el campo EmployeeName
.
EmployeeName
------------
Anand
Anand
Anil
Dipak
Anil
Dipak
Dipak
Anil
Usando una consulta, quiero eliminar los registros que se repiten.
¿Cómo se puede hacer esto con TSQL en SQL Server?
9 answers
Puede hacer esto con las funciones de ventana. Ordenará los dupes por EmpID, y eliminará todos menos el primero.
delete x from (
select *, rn=row_number() over (partition by EmployeeName order by empId)
from Employee
) x
where rn > 1;
Ejecútelo como una selección para ver lo que se eliminaría:
select *
from (
select *, rn=row_number() over (partition by EmployeeName order by empId)
from Employee
) x
where rn > 1;
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2010-07-23 15:22:00
Suponiendo que su tabla de empleados también tiene una columna única (ID
en el ejemplo a continuación), lo siguiente funcionará:
delete from Employee
where ID not in
(
select min(ID)
from Employee
group by EmployeeName
);
Esto dejará la versión con el ID más bajo en la tabla.
Editar
Re Mcgyver's comment-a partir de SQL 2012
MIN
se puede usar con columnas numéricas, char, varchar, uniqueidentifier o datetime, pero no con columnas de bits
Para 2008 R2 y anteriores,
MIN se puede usar con columnas numéricas, char, varchar o datetime, pero no con columnas de bits (y tampoco funciona con GUID)
Para 2008R2 necesitará convertir el GUID
a un tipo soportado por MIN
, por ejemplo,
delete from GuidEmployees
where CAST(ID AS binary(16)) not in
(
select min(CAST(ID AS binary(16)))
from GuidEmployees
group by EmployeeName
);
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2014-02-22 06:30:27
Podrías intentar algo como lo siguiente:
delete T1
from MyTable T1, MyTable T2
where T1.dupField = T2.dupField
and T1.uniqueField > T2.uniqueField
(esto asume que tiene un campo único basado en enteros)
Personalmente, aunque yo diría que es mejor tratar de corregir el hecho de que las entradas duplicadas se están agregando a la base de datos antes de que ocurra en lugar de como una operación post fix-it.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2010-07-23 11:07:27
DELETE
FROM MyTable
WHERE ID NOT IN (
SELECT MAX(ID)
FROM MyTable
GROUP BY DuplicateColumn1, DuplicateColumn2, DuplicateColumn3)
WITH TempUsers (FirstName, LastName, duplicateRecordCount)
AS
(
SELECT FirstName, LastName,
ROW_NUMBER() OVER (PARTITIONBY FirstName, LastName ORDERBY FirstName) AS duplicateRecordCount
FROM dbo.Users
)
DELETE
FROM TempUsers
WHERE duplicateRecordCount > 1
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2013-08-21 07:24:53
WITH CTE AS
(
SELECT EmployeeName,
ROW_NUMBER() OVER(PARTITION BY EmployeeName ORDER BY EmployeeName) AS R
FROM employee_table
)
DELETE CTE WHERE R > 1;
La magia de las expresiones de tabla comunes.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2014-08-07 21:10:30
Intenta
DELETE
FROM employee
WHERE rowid NOT IN (SELECT MAX(rowid) FROM employee
GROUP BY EmployeeName);
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2013-10-02 10:49:05
Si está buscando una manera de eliminar duplicados, pero tiene una clave externa que apunta a la tabla con duplicados, podría tomar el siguiente enfoque utilizando un cursor lento pero efectivo.
Reubicará las claves duplicadas en la tabla de claves foráneas.
create table #properOlvChangeCodes(
id int not null,
name nvarchar(max) not null
)
DECLARE @name VARCHAR(MAX);
DECLARE @id INT;
DECLARE @newid INT;
DECLARE @oldid INT;
DECLARE OLVTRCCursor CURSOR FOR SELECT id, name FROM Sales_OrderLineVersionChangeReasonCode;
OPEN OLVTRCCursor;
FETCH NEXT FROM OLVTRCCursor INTO @id, @name;
WHILE @@FETCH_STATUS = 0
BEGIN
-- determine if it should be replaced (is already in temptable with name)
if(exists(select * from #properOlvChangeCodes where Name=@name)) begin
-- if it is, finds its id
Select top 1 @newid = id
from Sales_OrderLineVersionChangeReasonCode
where Name = @name
-- replace terminationreasoncodeid in olv for the new terminationreasoncodeid
update Sales_OrderLineVersion set ChangeReasonCodeId = @newid where ChangeReasonCodeId = @id
-- delete the record from the terminationreasoncode
delete from Sales_OrderLineVersionChangeReasonCode where Id = @id
end else begin
-- insert into temp table if new
insert into #properOlvChangeCodes(Id, name)
values(@id, @name)
end
FETCH NEXT FROM OLVTRCCursor INTO @id, @name;
END;
CLOSE OLVTRCCursor;
DEALLOCATE OLVTRCCursor;
drop table #properOlvChangeCodes
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2016-09-28 06:57:47
Esta es una buena manera de deduplicar registros en una tabla que tiene una columna de identidad basada en una clave primaria deseada que puede definir en tiempo de ejecución. Antes de comenzar, rellenaré un conjunto de datos de muestra para trabajar con el siguiente código:
if exists (select 1 from sys.all_objects where type='u' and name='_original')
drop table _original
declare @startyear int = 2017
declare @endyear int = 2018
declare @iterator int = 1
declare @income money = cast((SELECT round(RAND()*(5000-4990)+4990 , 2)) as money)
declare @salesrepid int = cast(floor(rand()*(9100-9000)+9000) as varchar(4))
create table #original (rowid int identity, monthyear varchar(max), salesrepid int, sale money)
while @iterator<=50000 begin
insert #original
select (Select cast(floor(rand()*(@endyear-@startyear)+@startyear) as varchar(4))+'-'+ cast(floor(rand()*(13-1)+1) as varchar(2)) ), @salesrepid , @income
set @salesrepid = cast(floor(rand()*(9100-9000)+9000) as varchar(4))
set @income = cast((SELECT round(RAND()*(5000-4990)+4990 , 2)) as money)
set @iterator=@iterator+1
end
update #original
set monthyear=replace(monthyear, '-', '-0') where len(monthyear)=6
select * into _original from #original
A continuación crearé un Tipo llamado ColumnNames:
create type ColumnNames AS table
(Columnnames varchar(max))
Finalmente crearé un proc almacenado con las siguientes 3 advertencias: 1. El proc tomará un parámetro requerido @nombre_tabla que define el nombre de la tabla de la que está borrando en tu base de datos. 2. El proc tiene un parámetro opcional @columns que puede usar para definir los campos que conforman la clave primaria deseada que está eliminando. Si este campo se deja en blanco, se asume que todos los campos además de la columna de identidad conforman la clave primaria deseada. 3. Cuando se eliminan los registros duplicados, se mantendrá el registro con el valor más bajo en su columna de identidad.
Aquí está mi delete_dupes almacenado proc:
create proc delete_dupes (@tablename varchar(max), @columns columnnames readonly)
as
begin
declare @table table (iterator int, name varchar(max), is_identity int)
declare @tablepartition table (idx int identity, type varchar(max), value varchar(max))
declare @partitionby varchar(max)
declare @iterator int= 1
if exists (select 1 from @columns) begin
declare @columns1 table (iterator int, columnnames varchar(max))
insert @columns1
select 1, columnnames from @columns
set @partitionby = (select distinct
substring((Select ', '+t1.columnnames
From @columns1 t1
Where T1.iterator = T2.iterator
ORDER BY T1.iterator
For XML PATH ('')),2, 1000) partition
From @columns1 T2 )
end
insert @table
select 1, a.name, is_identity from sys.all_columns a join sys.all_objects b on a.object_id=b.object_id
where b.name = @tablename
declare @identity varchar(max)= (select name from @table where is_identity=1)
while @iterator>=0 begin
insert @tablepartition
Select distinct case when @iterator=1 then 'order by' else 'over (partition by' end ,
substring((Select ', '+t1.name
From @table t1
Where T1.iterator = T2.iterator and is_identity=@iterator
ORDER BY T1.iterator
For XML PATH ('')),2, 5000) partition
From @table T2
set @iterator=@iterator-1
end
declare @originalpartition varchar(max)
if @partitionby is null begin
select @originalpartition = replace(b.value+','+a.type+a.value ,'over (partition by','') from @tablepartition a cross join @tablepartition b where a.idx=2 and b.idx=1
select @partitionby = a.type+a.value+' '+b.type+a.value+','+b.value+') rownum' from @tablepartition a cross join @tablepartition b where a.idx=2 and b.idx=1
end
else
begin
select @originalpartition=b.value +','+ @partitionby from @tablepartition a cross join @tablepartition b where a.idx=2 and b.idx=1
set @partitionby = (select 'OVER (partition by'+ @partitionby + ' ORDER BY'+ @partitionby + ','+b.value +') rownum'
from @tablepartition a cross join @tablepartition b where a.idx=2 and b.idx=1)
end
exec('select row_number() ' + @partitionby +', '+@originalpartition+' into ##temp from '+ @tablename+'')
exec(
'delete a from _original a
left join ##temp b on a.'+@identity+'=b.'+@identity+' and rownum=1
where b.rownum is null')
drop table ##temp
end
Una vez que esto es cumplido, puede eliminar todos sus registros duplicados ejecutando el proc. Para eliminar duplicados sin definir una clave primaria deseada use esta llamada:
exec delete_dupes '_original'
Para eliminar duplicados basados en una clave primaria deseada definida use esta llamada:
declare @table1 as columnnames
insert @table1
values ('salesrepid'),('sale')
exec delete_dupes '_original' , @table1
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2018-03-13 19:45:02
Por favor, consulte la siguiente forma de eliminación también.
Declare @Employee table (EmployeeName varchar(10))
Insert into @Employee values
('Anand'),('Anand'),('Anil'),('Dipak'),
('Anil'),('Dipak'),('Dipak'),('Anil')
Select * from @Employee
Creó una tabla de muestra llamada @Employee
y la cargó con los datos dados.
Delete aliasName from (
Select *,
ROW_NUMBER() over (Partition by EmployeeName order by EmployeeName) as rowNumber
From @Employee) aliasName
Where rowNumber > 1
Select * from @Employee
Resultado:
Lo sé, esto se preguntó hace seis años, la publicación sólo en caso de que sea útil para cualquier persona.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2016-09-28 10:44:43