Clearing identical rows with SQL

Question

How to clear the database of fields identical in certain rows so that only one copy of each set of identical rows remains?

I have a table:

    CREATE TABLE table1 (field1 varchar(255), field2 varchar(255));

I would like to clean table1up any unnecessary copies of the lines, leaving a random line from each set of lines with the same field1.

UPD : send MySQL compatible commands.

Answer 1

- ALTER IGNORE. , , . IGNORE , . , , , . , .

ALTER IGNORE TABLE table1 ADD UNIQUE INDEX indexname (field1, field2)

Answer 2

MySQL:

CREATE TABLE `new_table` LIKE `table1`;
INSERT INTO `new_table` ( SELECT * FROM `table1` GROUP BY field1 );
DROP TABLE `table1`;
RENAME TABLE `new_table` TO `table1`;

"" , , , .

, , GROUP BY.

:

Answer 3

Fragsworth, :

• : NEW_TABLE
• field1
• NEW_TABLE
• NEW_TABLE

1, .

Answer 4

(, SQL Server):

SELECT field1, field2
INTO #temp
FROM 
   (SELECT ROW_NUMBER() OVER (PARTITION BY field1 ORDER BY NEWID()) AS __ROW, *
    FROM table1) x
WHERE x.__ROW = 1;

DELETE table1;

INSERT table1 
SELECT field1, field2
FROM #temp;

Answer 5

, ,

---

:

, . , .

 CREATE TABLE new_test (field1 INTEGER, field2 INTEGER);
    INSERT INTO new_test(field1,field2) SELECT DISTINCT field1,field2 FROM test;
    DROP TABLE test;
    RENAME TABLE new_test test;

, , >, . , :

:

ALTER TABLE t2 ADD COLUMN (pk INTEGER NOT NULL AUTO_INCREMENT, PRIMARY KEY(pk));

In any case, now you can make an independent connection and save MIN (pk):

Merge and delete duplicates:

mysql> DELETE dups.* FROM t2 AS dups
           INNER JOIN (
               SELECT field1,field2,MIN(pk) as MPK FROM t2
               GROUP BY field1,field2 HAVING COUNT(*) > 1 ) AS keep
           ON keep.field1=dups.field1
              AND keep.field2=dups.field2
              AND keep.MPK <> dups.pk;

Answer 6

You can use the syntax MYSQL ALTER IGNORE . The following command will remove any duplicates and leave a random line:

alter ignore table table1 add unique index index1 (field1);

It would be wise to keep the index in place, so new duplicates cannot be added. But if you want, you can remove the index with:

alter table table1 drop index index1;