subconsulta – devops Mauro Bernal

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:

eliminar repetidos con tsql

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

eliminar repetidos con tsql y row_number()

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

Eliminiar repetidos TSQL 31 repetidos 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…

Listado de la etiqueta: subconsulta

Eliminar Repetidos con TSQL

Eliminar Repetidos con TSQL y la función Row_Number()

Cargaremos una tabla de ejemplo MYTABLA

Buscando repetidos

Buscando los repetidos

Eliminado los repetidos

PBX Asterisk

Desarrollo de Software

Formas de Comunicarte

Links