Como borrar registros duplicados de SQL (the ultimate solution)
El dia de hoy en mi trabajo se me presento un problema con registros duplicados de una tabla de SQL server, asi que me di a la tarea que aqueja a todo DBA alguna vez en su vida. Como borrar registros duplicados de una tabla? (yo no soy DBA, por cierto, pero aqui hay que hacerla de todo).
En principio de cuentas, una registro duplicado nos habla de un mal diseño, o de base de datos o de procedimientos. En este caso el problema es un error de procedimiento, pero por ahora no hablaremos de eso.
Mi caso esta asi: Tengo una tabla con ID numerico, y un numero x de campos. De esos campos, los registros duplicados son iguales a excepcion de 1 campo (aparte del ID, por supuesto). La tabla tiene algo mas de 4 millones de registros, y habia unos 400,000 duplicados (algunos mas de 6 veces).
Googleando un poco encontre varias soluciones que van desde hacer un Select distinct a una tabla temporal, vaciar la tabla original y luego copiar lo de la temporal a la original (lo cual resulta muy lento), hasta un script que hacia uso de cursores, y que nunca pude hechar a andar.
Todo fue hasta que por ahi me encontre con una solucion simple, rapida y efectiva, que solo requiere que tu tabla contenga un ID numerico. Si no lo tiene, pues lo puedes agregar facilmente, y si despues no lo requieres, puedes borrarlo.
De lo que se trata es de seleccionar la ID mas baja (o mas alta) de cada conjunto de duplicados (tenemos que definir que es un duplicado), y borrar las demas. Simple, no?
Para ejemplificarlo vamos a suponer que tenemos nuestra tabla “Tabla1″. Esta tabla tiene los campos “ID”, “Campo1″, Campo2″ y “Campo3″. Lo primero que tenemos que hacer es identificar que campos tienen que ser iguales para que un registro sea repetido. Puede ser que sean todos los campos o solo un conjunto de estos. En este ejemplo vamos a decir que Campo2 y 3 son necesarios. Una vez identificados lo que tenemos que hacer es ejecutar la siguiente consulta.
delete from Tabla1 where Id >
(
Select min(Id) from Tabla1 Tbl1 where Tabla1.Campo2 = Tbl1.Campo2 and Tabla1.Campo3 = Tbl1.Campo3
)
Es todo! Simple, no? Funciona igual si cambias el signo por “<” y usas “max” en lugar de “min”. En este caso en lugar de conservar el Id mas baja, conservas la mas alta.
Simple y rapido ademas. Mi consulta demoro poco mas de un minuto en buscar y borrar los duplicados en mi tabla de 4 millones. Espero que esta solucion los salve de mas de una. ∞
January 11th, 2008 at 10:47 am
Excelente solución, mil gracias!!!
February 8th, 2008 at 4:05 pm
excelentisimo, me ayudo bastante
muchas gracias
y felicidades por tu website.
March 4th, 2008 at 11:59 am
a mi me sale error no se porque ,,, porfa ayudam..por ejemplo:
select min(id) from ventas Tv
where
ventas.cliente = Tv.cliente(hay mas columnas pero el error es igual)
no me hace caso y sale error:
Server: Msg 107, Level 16, State 3, Line 1
The column prefix ‘ventas’ does not match with a table name or alias name used in the query.
no compara ni filtra los dup
gracias
June 10th, 2008 at 1:29 am
Esta solución está bien para un caso normal (sin tablas mal diseñadas). La verdad es que es útil y sencillo.
Lo malo es si el campo clave no está definido como tal, y está duplicado también; en ese caso no serviría… y esto pasa a menudo, por desgracia.
Entonces si que tendrás que pasar lo bueno a una tabla temporal, etc.
June 18th, 2008 at 10:08 pm
No, de hecho, sin tablas mal diseñadas, de hecho, no habria necesidad de esto, pues una tabla bien diseñada segun las 3 formas normales, no deberia de tener registros duplicados.
Si hay un campo llave mal definido, pues de hecho es como si no existiera uno. Y la tecnica dice que hay que agregar una nueva columna numerica que nos servira como llave temporal para aplicar esta tecnica
June 26th, 2008 at 2:01 pm
Excelente solución!!!
June 26th, 2008 at 2:02 pm
Excelente solución!!!! Me gustaría saber cuales fueron tus fuentes de consulta para llegar a esto tan simple.
June 26th, 2008 at 8:37 pm
El simple y confiable Google!
August 14th, 2008 at 3:22 pm
Esta solucion funciona, pero!!!!
Si hablaramos de una tabla que tiene un campo clave que se repite y la diferencia de cada registro es una fecha, y solo quisiera dejar los registros sinrepetir con fecha mas alta?
Ya no es tan sencillo verdad?
Si alguien sabe como hacer esto se los agradecere eternamente.
Saludos desde SLP México
August 21st, 2008 at 11:16 am
No, de hecho es exactamente lo mismo. Lo unico que tienes que hacer es agregar otro campo a la tabla, autonumerico para asegurarte que sea unico, y basar en el el borrado de duplicados.
August 21st, 2008 at 11:37 am
Exelente solucion.
Aunque no la utilice para eliminar si no para hacer una consulta
igual se agradece, me sirvio mucho
October 10th, 2008 at 11:29 am
muchas gracias, realamente me ayudo bastante
November 11th, 2008 at 3:01 am
Perfecto!!! me has ayudado mucho, también había encontrado la solcuión de la tabla temporal, pero como que no, era demasiado complicado, ésta sin embargo es…. tan simple…
November 22nd, 2008 at 10:42 am
se pueden apoyar de rowid como campo clave, este segun yo no se repite
February 9th, 2009 at 2:51 pm
Hace mas de un año postease esto
y no sabes como me acabas de salvar la vida
se pura casualidad la tabla tenia un idconsecutivo
y me quedo como anillo al dedo
por que yo no hubiera podido modificar la tabla sin pedir permiso a media empresa
Saludos
February 18th, 2009 at 9:27 am
Exelente solucion …. hay que tener en cuenta que para sqlserver se presenta el error que reportaron antes, simplemente hay que cambiar el nombre de la tabla por el alias que se asignó a la misma
February 23rd, 2009 at 4:22 am
Excelente!!!
Me ha funcionado a la perfeccion.
February 24th, 2009 at 11:51 am
muchas gracias por la ayuda, me sirvio bastante ya que el distinct no sirve cuando los registros tienen fechas
February 25th, 2009 at 5:34 am
Y si tuviera que borrar los de mayor id pero guardar un campo y hacer el update de ese campo en la fila que nos quedamos, se podria??
Gracias.
March 3rd, 2009 at 5:32 pm
brillante muy bueno unos pocos segundo y solucionado
March 11th, 2009 at 1:26 pm
Muy buena solucion, pero eso nos sirve cuando existen 2 campos duplicados en un mismo registro, pero no para el caso cuando tenemos ID duplicadas, o dicho de otra manera, cuando hay 2 o mas clientes con el mismo ID, eso generalmente llega cuando se reciben bases de datos sin normalizar y hay que insertar o actualizar en una base de datos normalizada.
Saludos
March 23rd, 2009 at 12:02 pm
Muchisimas gracias… un gran aporte de tu parte
March 24th, 2009 at 6:19 pm
Tengo una pregunta, si la tabla que quieres limpiar de duplicacos tuviera un parametro mas, una fecha, es decir, que tuvieras que tener en cuenta la fecha para la eliminacion de las filas.
Ejemplo:
Eliminar todos los duplicados de X codigo de Barras, solo quedando la fila con la fecha mas actual.
March 24th, 2009 at 6:44 pm
SOLUCIONADO, jeje, solo cambie el ID por un campo fecha y Listo, quedo asi:
delete from Tabla where FECHA
March 25th, 2009 at 11:10 am
Me sacaste de un tremendo apuro…Gracias.
April 10th, 2009 at 12:07 pm
Para Potosino:
Funciona igual si colocas omo min o max la fecha, yo tuve ese problema y con la ayuda de este excelente query resolví eliminar los registros repetidos donde mi común era un ID único, y el campo de referencia para la eliminación de los registros duplicados es la fecha mayor, y que se quedara el registro que tuviera la fecha menor. quedó asi:
delete from tabla where fecha >
(
Select min(fecha) from tabla Tbl1
where tabla.id = Tbl1.id
)
Excelente query el que compartes amigo, simplemente excelente.
July 7th, 2009 at 11:45 am
funciona a la perfección
yo le tuve que hacer unas adecuaciones pero el resultado fue optimo 
September 17th, 2009 at 11:00 am
esta mal esta query, me borro todos los registros que se repetian, no me dejo nada.
September 30th, 2009 at 3:18 pm
Y en caso, no tenga un id porque no han normalizado la bd…como puedo recorrer cada celda pero sin usar cursores…ya que esto pone lenta la aplicacion
October 9th, 2009 at 11:13 am
Perfecto! Justo lo que buscaba! Gracias
December 11th, 2009 at 3:23 am
de donde saca Tbl1???
December 18th, 2009 at 8:57 am
Disculpen la ignorancia, pero Tbl1 a que hace referencia???
February 3rd, 2010 at 7:14 am
Funciona bien!! pero la verdad que no entiendo porque.
Se supone que el delete externo exterminará todo los de ID mayor… TODOS, sin importar si son repetidos o no, ya que la comparación se hace en la subconsulta, pero no…. funciona ok.
Alguien me lo aclara?
February 3rd, 2010 at 11:09 am
Hola…al igual q alex..no entiendo a que hace referencia tbl1? por favor me lo aclaras?? Gracias
February 16th, 2010 at 4:18 pm
El Tbl1 se refiere a Tabla1, solo que Tbl1 funciona como un alias
February 18th, 2010 at 4:50 am
Muchísimas gracias por esta solución es fantástica.
February 22nd, 2010 at 10:07 pm
la mejor solucion en todo google
perfecto amigo.
February 27th, 2010 at 8:56 am
hola a todos
este es mi query
SELECT IdPersona,AlumPersona,AcudPersona
FROM cl_persona
WHERE IdPersona >
( SELECT min(IdPersona) FROM cl_persona bl1
WHERE cl_persona.AlumPersona=bl1.AlumPersona
and cl_persona.AcudPersona=bl1.AcudPersona );
El query funciona con SELECT pero cuando lo hago con DELETE sale esto:
Error Code : 1093
You can’t specify target table ‘cl_persona’ for update in FROM clause
(0 ms taken)
April 26th, 2010 at 2:13 pm
Estas seguro q funciona esta solucion??, me parece que no
te dire porque:
Si mi tabla tiene estos registros
Id campo 1
01 rojo
01 rojo
02 amarillo
02 amarillo
03 verde
hasta ahora no se como sacando el “minimo” vas a eliminar registros repetidos.
April 27th, 2010 at 4:59 pm
Hola a todos:
A mi me facilito detectar los registros duplicados con la función HAVING. Allí les dejo un ejemplo
SELECT c1,c2 FROM MiTabla
GROUP BY c1,c2
HAVING count(*) > 1
April 28th, 2010 at 4:36 pm
Muy buen script. La tabla en la cual requería eliminar los registros duplicados no tiene un campo identity así que filtré los registros duplicados, los almacené en una tabla temporal, apliqué tu script y luego generé una sentencia delete usando un select a la tabla temporal.
Gracias
August 9th, 2010 at 3:04 pm
Exelente, funciona a la perfeccion, en mi caso cree un indice agrupado para ordenar los registros duplicados fisicamente, luego añadi un campo identity, ejecute el script y elimine el identity, y listo….