sql delete duplicate rows but keep one

Question

in my sql I trying delete duplicate rows but just keep one with a very large amount of rows like over 100,000 rows

[mysql workbench image]

Id Acc_# time Lname Fname
6537 12345 20190101 john Tim
6537 12345 20190101 john Tim
6537 12345 20190101 john Tim
6537 12345 20190101 john Tim
6537 12345 20190101 john Tim
6538 54321 20190102 bob james
6538 54321 20190102 bob james
6538 54321 20190102 bob james
6538 54321 20190102 bob james

Does this answer your question? [Remove duplicate rows in MySQL](https://stackoverflow.com/questions/3311903/remove-duplicate-rows-in-mysql) Also see [Why should I provide a Minimal Reproducible Example for a very simple SQL query?](https://meta.stackoverflow.com/q/333952) — Ken White, May 25 '21 at 20:21

score 0 · Answer 1 · answered May 25 '21 at 20:20

0

It's probably going to be simplest to run these 3:

CREATE TABLE t2 SELECT DISTINCT * FROM t1

TRUNCATE TABLE t1

INSERT INTO t1 SELECT * FROM t2

answered May 25 '21 at 20:20

Caius Jard

72,509
5
49
80

score 0 · Answer 2 · answered May 25 '21 at 20:21

If I may suggest: I think you best bet is to use some sort of temporary table:

select distinct [id], [Acc_#], [time], [Lname], [Fname] into [dbo].[temp] from [yourtablename];

Then drop the original table and rename the temp table.

Viable alternatives would need to include some additional column that makes your data (temporarily) unique, like adding an identity column or similar

sql delete duplicate rows but keep one

2 Answers2