Deprecated: Function get_magic_quotes_gpc() is deprecated in /home/styllloz/public_html/qa-include/qa-base.php on line 1175

Warning: session_start(): Cannot start session when headers already sent in /home/styllloz/public_html/qa-include/app/users.php on line 162

Warning: Cannot modify header information - headers already sent by (output started at /home/styllloz/public_html/qa-include/qa-base.php:1175) in /home/styllloz/public_html/qa-include/app/users.php on line 1267

Warning: Cannot modify header information - headers already sent by (output started at /home/styllloz/public_html/qa-include/qa-base.php:1175) in /home/styllloz/public_html/qa-include/app/page.php on line 356
MYSQL. To remove the duplicate rows? - code-flow.club | Q&A

MYSQL. To remove the duplicate rows?


Warning: count(): Parameter must be an array or an object that implements Countable in /home/styllloz/public_html/qa-theme/donut-theme/qa-donut-layer.php on line 274
0 like 0 dislike
7 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 | 7 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
35,182 users