MYSQL. To remove the duplicate rows?


Warning: count(): Parameter must be an array or an object that implements Countable in /home/styllloz/code-flow.club/qa-theme/donut-theme/qa-donut-layer.php on line 274
0 like 0 dislike
5 views
There are tablica with 100,000 rows.

Need to delete duplicate rows(that is rows and not cells)

so from the table:

c1 c2

1 2

1 0

4 2

1 0

1 1

2 1


Left:

c1 c2

1 2

1 0

4 2

1 1

2 1


One line (1 0) you need to remove as it was 2.

Can it be done without a query or php can not do?

Thank you.
by | 5 views

7 Answers

0 like 0 dislike
CREATE TEMPORARY TABLE tmp_tab AS SELECT DISTINCT * FROM your_table;
\r
DELETE FROM your_table;
\r
INSERT INTO your_table SELECT * FROM tmp_tab;
\r
DROP TABLE tmp_tab;
by
0 like 0 dislike
You can still bzzz ALTER IGNORE TABLE tmp add unique index(column1, column2)
by
0 like 0 dislike
Helped me here is an option:
\rALTER IGNORE TABLE table1 ADD UNIQUE KEY(Name2, Name3);
by
0 like 0 dislike
Add primary key (id) and then:
\r
DELETE FROM table as me, table as clone WHERE me.c1 = clone.c1 AND me.c2 = clone.c2 AND me.id < clone.id
\r
\rIt works if I correctly understood the task.
by
0 like 0 dislike
I like this method:
Create table dst with the same structure, but add the digital field DupCount
\r
do request:
INSERT INTO dst SELECT * FROM src ON DUPLICATE KEY UPDATE dst.DupCount = dst.DupCount +1
\r
DupCount for each row will contain the number of repeats encountered.
\r
You can write a script that will create table dst, copy, thus there is data, delete the original table and rename dst to src.
by
0 like 0 dislike
by
0 like 0 dislike
I can say that DISCINCT it is really a mistake of all databases and glances at him it is always necessary, but in such a simple but not standard tasks is the best option.
by

Related questions

0 like 0 dislike
4 answers
asked Mar 20, 2019 by camokatik
0 like 0 dislike
4 answers
0 like 0 dislike
3 answers
0 like 0 dislike
1 answer
asked Apr 9, 2019 by ART4
0 like 0 dislike
5 answers
110,608 questions
257,186 answers
0 comments
23,593 users