Estoy limpiando una tabla de base de datos sin una clave principal (yo sé, yo sé, ¿qué estaban pensando?). No se puede agregar una clave principal, porque no es un duplicado en la columna que se convertiría en la clave. El valor duplicado proviene de una de las dos filas que están en todos los aspectos idénticos. No puedo borrar la fila a través de una interfaz gráfica de usuario (en este caso MySQL Workbench, pero estoy buscando una base de datos agnóstico enfoque) porque se niega a realizar tareas en las tablas sin necesidad de claves primarias (o, al menos, un UQ NN columna), y no se puede agregar una clave principal, porque no es un duplicado en la columna que se convertiría en la clave. El valor duplicado viene de uno…

¿Cómo puedo eliminar uno de los gemelos?

  • ¿cuántos de esos duplicados existen?
  • en el contexto original de esta pregunta, sólo una o dos he descubierto otra mesa con un montón de duplciates una estrategia como stackoverflow.com/a/3777663/236081 probablemente será la más adecuada

15 Comentarios

  1. 22

    Una opción para solucionar su problema es crear una tabla nueva con el mismo esquema y, a continuación, hacer:

    INSERT INTO new_table (SELECT DISTINCT * FROM old_table)

    y, a continuación, simplemente cambie el nombre de las tablas.

    Tendrá aproximadamente la misma cantidad de espacio como de su mesa requiere de repuesto en su disco para hacer esto!

    No es eficiente, pero es increíblemente simple.

    • Todavía creo que mi colega de la solución (en mi respuesta) es limpio, pero el tuyo es de hecho muy simple y explicar las salvedades bien
    • hmmm, esto realmente no funciona 🙁 por alguna razón new_table contenidos de todo, desde old_table – ¿alguna idea?
    • Yo sólo lo probé en un trivial de la tabla y funcionó exactamente como se esperaba
    • debe haber sido yo – me hizo stackoverflow.com/a/3312066/236081 en el entretanto, que también trabajó (ésta no es una base de datos de producción, así que no cuenta las advertencias)
  2. 52
    SET ROWCOUNT 1
    DELETE FROM [table] WHERE ....
    SET ROWCOUNT 0

    Esto sólo va a eliminar una de las dos filas iguales

  3. 19

    Tenga en cuenta que MySQL tiene su propia extensión de DELETE, que es DELETE ... LIMIT, que funciona de la forma habitual que usted esperaría de LIMIT: http://dev.mysql.com/doc/refman/5.0/en/delete.html

    Las específicas de MySQL LIMIT row_count opción para ELIMINAR indica al servidor
    el número máximo de filas que ser eliminado antes de que el control se devuelve a
    el cliente. Esto puede ser usado para asegurar que una determinada instrucción DELETE
    no toma demasiado tiempo. Usted puede simplemente repetir la eliminación
    declaración hasta que el número de filas afectadas es menor que el LÍMITE
    valor.

    Por lo tanto, usted podría utilizar DELETE FROM some_table WHERE x="y" AND foo="bar" LIMIT 1; tenga en cuenta que no hay una manera simple de decir «borrar todo excepto uno» – acaba de mantener la comprobación de si usted todavía tiene fila de duplicados.

    • es bueno saber eso, lo que he tratado de plantear mi pregunta en una base de datos agnóstico manera porque, quién sabe, yo podría estar tratando con PostgreSQL próxima vez, ¿sabes si esto es una extensión común?
    • MySQL solo, lo siento. (Todavía tengo que ver esta implementado en cualquier otro lugar)
  4. 10

    Para PostgreSQL usted puede hacer esto:

    DELETE FROM tablename
    WHERE id IN (SELECT id
              FROM (SELECT id, ROW_NUMBER() 
                   OVER (partition BY column1, column2, column3 ORDER BY id) AS rnum
                   FROM tablename) t
              WHERE t.rnum > 1);

    columna1, columna2, columna3 sería el conjunto de columnas que tienen valores duplicados.

    Referencia aquí.

    • Aunque esto, en teoría, puede responder a la pregunta, sería preferible para incluir las partes esenciales de la respuesta aquí, y proporcionar el enlace de referencia.
    • Gracias por la sugerencia, editado mi respuesta 🙂
    • No esta todavía requieren id a ser único entre las filas?
    • Yo creo que esta respuesta es también cumplen con las normas, por lo que probablemente debería funcionar con casi todos los modernos RDBMS.
  5. 5

    Esto puede lograrse mediante el uso de una CTE y la ROW_NUMBER() función, de la siguiente manera:

    /* Sample Data */
        CREATE TABLE #dupes (ID INT, DWCreated DATETIME2(3))
    
        INSERT INTO #dupes (ID, DWCreated) SELECT 1, '2015-08-03 01:02:03.456'
        INSERT INTO #dupes (ID, DWCreated) SELECT 2, '2014-08-03 01:02:03.456'
        INSERT INTO #dupes (ID, DWCreated) SELECT 1, '2013-08-03 01:02:03.456'
    
    /* Check sample data - returns three rows, with two rows for ID#1 */
        SELECT * FROM #dupes 
    
    /* CTE to give each row that shares an ID a unique number */
        ;WITH toDelete AS
          (
            SELECT ID, ROW_NUMBER() OVER (PARTITION BY ID ORDER BY DWCreated) AS RN
            FROM #dupes 
          )
    
      /* Delete any row that is not the first instance of an ID */
        DELETE FROM toDelete WHERE RN > 1
    
    /* Check the results: ID is now unique */
        SELECT * FROM #dupes
    
    /* Clean up */
        DROP TABLE #dupes

    Tener una columna para ORDENAR POR es útil, pero no es necesario a menos que usted tiene una preferencia por que de las filas a eliminar. Esto también se encargará de todas las instancias de registros duplicados, en lugar de forzar a eliminar una fila a la vez.

    • La mejor solución aquí en mi humilde opinión. 🙂
  6. 4

    Tratado de LÍMITE 1? Esto sólo se elimina 1 de las filas que coinciden con su DELETE consulta

    DELETE FROM `table_name` WHERE `column_name`='value' LIMIT 1;
    • SQL Server no acepta para mí…dijo: sintaxis incorrecta cerca de ‘límite’
  7. 2

    Usted podría utilizar un máximo, que fue importante en mi caso.

    DELETE FROM [table] where id in 
    (select max(id) from [table] group by id, col2, col3 having count(id) > 1)

    Asegúrese de comprobar los resultados de la primera y tener una condición limitante en su «haber» clausule. Con una enorme cantidad de eliminar de la consulta es posible que desee actualizar su base de datos.

  8. 1

    En mi caso me podría obtener la interfaz gráfica de usuario para darme una cadena de valores de la fila en cuestión (como alternativa, podría haber hecho esto con la mano). En la sugerencia de un colega, en cuya deuda que me quedan, he usado esto para crear una instrucción INSERT:

    INSERT
    'ID1219243408800307444663', '2004-01-20 10:20:55', 'INFORMATION', 'admin' (...)
    INTO some_table;

    He probado la instrucción de inserción, de modo que ahora tenía trillizos. Por último, me encontré con un simple delete para ELIMINAR a todos ellos…

    DELETE FROM some_table WHERE logid = 'ID1219243408800307444663';

    seguido por la INSERCIÓN una vez más, me dejó con una sola fila, y el brillante posibilidades de una clave principal.

  9. 1

    en caso de que usted puede agregar una columna como

      ALTER TABLE yourtable ADD IDCOLUMN bigint NOT NULL IDENTITY (1, 1)

    hacerlo.

    a continuación, el recuento de filas de la agrupación por su problema de columna donde cuente >1 , esto va a identificar a tus gemelos o trillizos o lo que sea).

    a continuación, seleccione su problema de columna donde su contenido es igual al contenido identificado de arriba y compruebe el Id en IDCOLUMN.

    eliminar de la tabla donde IDCOLUMN es igual a uno de esos Identificadores.

  10. 1

    Esto funciona para PostgreSQL

    DELETE FROM tablename WHERE id = 123 AND ctid IN (SELECT ctid FROM tablename WHERE id = 123 LIMIT 1)
  11. 0

    He añadido una columna Guid a la mesa para generar un nuevo id para cada fila. Entonces yo podría eliminar las filas utilizando una interfaz gráfica de usuario.

  12. 0

    En PostgreSQL hay implícita una columna llamada ctid. Ver el wiki. Así que eres libre de usar lo siguiente:

    WITH cte1 as(
        SELECT unique_column, max( ctid ) as max_ctid
        FROM table_1
        GROUP BY unique_column
        HAVING count(*) > 1
    ), cte2 as(
        SELECT t.ctid as target_ctid
        FROM table_1 t
        JOIN cte1 USING( unique_column )
        WHERE t.ctid != max_ctid
    )
    DELETE FROM table_1
    WHERE ctid IN( SELECT target_ctid FROM cte2 )

    No estoy seguro de qué tan seguro es utilizar este cuando hay una posibilidad de actualizaciones simultáneas. Por lo que podemos encontrar es sensato hacer una LOCK TABLE table_1 IN ACCESS EXCLUSIVE MODE; antes de que realmente está haciendo la limpieza.

  13. 0

    En el caso de que existan varias filas duplicadas, eliminar y todos los campos son idénticos, no hay otro id, la tabla no tiene clave primaria , es una opción para guardar las filas duplicadas con distintas en una nueva tabla, eliminar todas las filas duplicadas e insertar las filas de atrás. Esto es útil si la tabla es muy grande, y el número de filas duplicadas es pequeño.

    ---  col1 , col2 ... coln are the table columns that are relevant. 
    --- if not sure add all columns of the table in the select bellow and the where clause later. 
    
    --- make a copy of the table T to be sure you can rollback anytime , if possible
    --- check the @@rowcount to be sure it's what you want
    --- use transactions and rollback in case there is an error 
    
    --- first find all with duplicate rows that are identical , this statement could be joined 
    --- with the first one if you choose all columns 
    
    select col1,col2, --- other columns as needed
      count(*) c into temp_duplicate group by col1,col2 having count(*) > 1 
    
    --- save all the rows that are identical only once ( DISTINCT ) 
    
    insert distinct * into temp_insert from T , temp_duplicate D where
    T.col1 = D.col1 and
    T.col2 = D.col2 --- and other columns if needed
    
    --- delete all the rows that are duplicate
    
    delete T from T , temp_duplicate D where 
    T.col1 = D.col1 and
    T.col2 = D.col2 ---- and other columns if needed
    
    --- add the duplicate rows , now only once
    insert into T select * from temp_insert 
    
    --- drop the temp tables after you check all is ok 
  14. 0

    Si, como yo, usted no quiere tener a la lista de todas las columnas de la base de datos, usted puede convertir cada fila para JSONB y comparar por que.

    (NOTA: Esto es increíblemente ineficiente – ¡ten cuidado!)

    select to_jsonb(a.*), to_jsonb(b.*)
    FROM
        table a
            left join table b
    on
        a.entry_date < b.entry_date
    where (SELECT NOT exists(
        SELECT
        FROM jsonb_each_text(to_jsonb(a.*) - 'unwanted_column') t1
             FULL OUTER JOIN jsonb_each_text(to_jsonb(b.*) - 'unwanted_column') t2 USING (key)
        WHERE t1.value<>t2.value OR t1.key IS NULL OR t2.key IS NULL
    ))

Dejar respuesta

Please enter your comment!
Please enter your name here