Eliminar Repetidos con TSQL y la función Row_Number()
Hoy vamos usar row_number(), una subconsulta, comando IN para buscar y eliminar registros repetidos.
En esta nueva etapa me he propuesto compartir cada uno de los scripts que siempre son de utilidad cuando se trabaja con bases de datos.
Como siempre son con TSQL para MS SQL Server.
Esta vez veremos como eliminar registros repetidos de una tabla, solo dejando 1 registro.
El secreto está en el uso del comando ROW_NUMBER
https://docs.microsoft.com/en-us/sql/t-sql/functions/row-number-transact-sql
Cargaremos una tabla de ejemplo MYTABLA
IF EXISTS(SELECT 1 FROM sys.tables WHERE object_id = OBJECT_ID('myTable'))
BEGIN;
DROP TABLE [myTable];
END;
GO
CREATE TABLE [myTable] (
[myTableID] INTEGER NOT NULL IDENTITY(1, 1),
[Documento] VARCHAR(13) NULL,
[Nombre] VARCHAR(255) NULL,
PRIMARY KEY ([myTableID])
);
GO
INSERT INTO myTable([Documento],[Nombre]) VALUES('1695071197799','Jin');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1630121141299','Harlan');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1637061077799','Armand');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1653081722499','Chadwick');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1695020909099','Fletcher');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1614102485499','Garth');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1624071487999','Cedric');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1678010341799','Prescott');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1695071197799','Jin');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1630121141299','Harlan');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1637061077799','Armand');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1653081722499','Chadwick');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1691081716399','Harding');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1641101376799','Jakeem');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1630020717199','Carlos');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1627101503999','Edan');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1636051993199','Samson');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1627041338699','Wylie');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1661032884599','Brennan');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1625120186899','Brett');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1642100764499','Ira');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1624052909599','Kyle');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1656062403199','Jordan');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1656041722899','Keane');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1630122181399','Cain');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1634062813199','Stone');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1615111655399','Elton');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1609092165099','Theodore');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1682111461299','Noble');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1656041722899','Keane');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1630122181399','Cain');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1634062813199','Stone');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1615111655399','Elton');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1609092165099','Theodore');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1682111461299','Noble');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1656041722899','Keane');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1630122181399','Cain');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1634062813199','Stone');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1615111655399','Elton');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1609092165099','Theodore');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1682111461299','Noble');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1656020101399','Alden');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1657080142799','Tanek');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1678122145299','Fulton');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1684010635799','Reese');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1613011065899','Grant');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1630012756499','Jakeem');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1618071462499','Flynn');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1615052518299','Benjamin');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1691052832399','Wylie');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1669013054199','Tucker');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1612110409199','Laith');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1614110752199','Tad');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1699041337299','Chaney');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1650112954899','Jeremy');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1647052552299','Adam');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1643041374799','Denton');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1642081947099','Knox');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1643020793199','Vance');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1632020546699','Thaddeus');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1670032188999','Keegan');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1615102660699','Clark');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1693053059399','Hedley');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1661082842999','Beau');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1636081862699','August');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1608070833799','Oscar');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1687121074299','Leo');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1655122056599','Griffith');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1614010191199','Andrew');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1668121357799','Abdul');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1691081716399','Harding');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1641101376799','Jakeem');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1630020717199','Carlos');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1627101503999','Edan');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1636051993199','Samson');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1627041338699','Wylie');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1661032884599','Brennan');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1625120186899','Brett');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1642100764499','Ira');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1602091000299','Jordan');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1602060613699','Guy');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1656041722899','Keane');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1630122181399','Cain');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1634062813199','Stone');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1615111655399','Elton');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1609092165099','Theodore');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1682111461299','Noble');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1649101947099','Bradley');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1692080607499','Basil');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1645013071199','Bruce');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1677121204299','Chancellor');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1668110667099','Price');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1644072576599','Driscoll');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1616101911399','August');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1680021349199','Wallace');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1652052495999','Emery');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1692050724999','Talon');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1614111114799','Avram');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1670010342799','Hiram');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1636022785999','Brody');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1609033047099','Stuart');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1669122235799','Moses');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1626080826599','Leo');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1670010510999','Micah');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1676043021599','Jonah');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1621032365499','Len');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1658020481699','Amir');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1611032912899','Len');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1638071718599','Ray');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1676120168199','Kenyon');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1688050847099','Sawyer');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1692051561099','Malcolm');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1664101012499','Nolan');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1682121754399','Lucius');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1633112425699','Alvin');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1606040846399','Ralph');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1610061348699','Jin');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1649120964999','Malachi');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1609121778799','Kermit');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1649111157999','Wade');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1625051934199','Simon');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1698060420799','Donovan');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1607101359699','Isaiah');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1648100677099','Avram');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1668081567999','Grant');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1613042285999','Keefe');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1626042455399','Joshua');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1676081014999','Amir');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1643042339599','Conan');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1695051340499','Jin');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1610012015699','Akeem');
Esta pequeña tabla de ejemplo tiene la siguiente estructura:
Buscando repetidos
Lo primero que haremos es buscar los que tienen el campo DOCUMENTO repetido usando la función Row_Number(). Esta nos devolverá un nuevo campo que llamaremos ORDEN, e indicara las veces que se repite un campo
select *,ROW_NUMBER() over ( PARTITION BY Documento ORDER BY myTableID) as Orden
from mytable
PARTITION BY Documento le indicamos que la partición sea el campo documento
ORDER BY myTableID le indicamos el criterio para enumerar. Es decir en orden ascendente por ID del campo. Si tuviésemos un campo de tipo fecha (Datetime) podríamos ordenar por mas nuevo, o viceversa
Buscando los repetidos
Lo primero que haremos es buscar los repetidos usando una subconsulta
Select myTableID from
(select *,ROW_NUMBER() over ( PARTITION BY Documento ORDER BY myTableID) as Orden
from mytable) SubConsulta1
where Orden>1
Esta consulta devuelve los ID de los repetidos: 31 registros para ser mas especificos
Eliminado los repetidos
Ahora los eliminamos usando el comando IN
begin transaction eliminacion1
delete from mytable where
mytableID in
(
Select myTableID from
(select *,ROW_NUMBER() over ( PARTITION BY Documento ORDER BY myTableID) as Orden
from mytable) SubConsulta1
where Orden>1
)
commit transaction eliminacion1
Espero que les haya sido útil.
El comando Row_Number() permite trabajar con los registros de forma masiva pudiendo identificar la N aparición.
Mas adelante veremos como eliminar / actualizar:
-El último registro de una serie de registros
-El primero, etc…
El segundo ejemplo es como debemos filtrar los campos con valores NULL
Que sigue…
Podremos ver el tiempo promedio estimado de mejora al realizar el índice, como el TSQL para crearlo.
Con un poco de ingenio podemos obtener un poco más
ts
