Only the first and first of the duplicate data are retained.

Only the first and first of the duplicate data are retained.

-- CREATE test TABLE [dbo]. [testtab] ([id] [nchar] (10) NULL, [name] [nchar] (10) NULL ); -- insert test data into the test table: insert into testtab values ('1', '1'); insert into testtab values ('1', '1 '); insert into testtab values ('2', '2'); insert into testtab values ('2', '2'); insert into testtab values ('3 ', '3'); insert into testtab values ('3', '3'); -- create a temporary table, insert data in the test table testtab to the temporary table, and add an auto-increment id: autoID select identity (int,) as autoID, * into # Tmp from testtab -- delete temporary table # repeated data in tmp according to autoID, only the first delete # Tmp where autoID in (select max (autoID) from # Tmp group by id) in each group of duplicate data is retained; -- delete testtab of all data in the testtab table; -- insert # insert into testtab select id, name from # Tmp; -- delete temporary table # Tmp drop table # Tmp;

How can I delete a duplicate row in SQL database and keep a record? Oh

Let's take a look at your table structure. Let me write a simple article for your reference:
Delete From table
Where Id not in (Select min (id), column1, column2 From table group by column1, column2)

The SQL database has multiple records that are completely duplicated. How can we delete duplicate records and keep only one record? How can we write SQL statements?

Delete from tablename where id not in (select max (id) from

Tablename group by col1, col2 ,...)