How to Delete All Duplicate Records from SQL Table

How to delete all duplicate records from SQL Table?

Try this

DELETE
FROM FriendsData 
WHERE fID NOT IN
(
SELECT MIN(fID)
FROM FriendsData 
GROUP BY UserID, FriendsID)

See here

Or here is more ways to do what you want

Hope this helps

Large SQL table delete all duplicates

The most efficient way is probably to keep one row per table in a new table. Then truncate the old table and re-insert the values:

select t.*  -- or select all columns except seqnum
into temp_table
from (select t.*,
             row_number() over (partition by email_address, job_id order by job_id) as seqnum
      from t
     ) t
where seqnum = 1;

-- optional if you remove seqnum above
-- alter table temp_table drop column seqnum;

-- back this up first!
truncate table t;

insert to t
    select * from temp_table;

You could drop the original table and rename temp_table to the table name. If you do so, remember to create constraints, indexes, triggers, partitions and whatever else is on the original table.

This will not be super fast (you can see how long it takes to create the temporary table). However, it does not require the locking and logging from deleting the original table. As a bonus, the new table should be less fragmented than if you did deletes.

T-SQL: Deleting all duplicate rows but keeping one

You didn't say what version you were using, but in SQL 2005 and above, you can use a common table expression with the OVER Clause. It goes a little something like this:

WITH cte AS (
  SELECT[foo], [bar], 
     row_number() OVER(PARTITION BY foo, bar ORDER BY baz) AS [rn]
  FROM TABLE
)
DELETE cte WHERE [rn] > 1

Play around with it and see what you get.

(Edit: In an attempt to be helpful, someone edited the ORDER BY clause within the CTE. To be clear, you can order by anything you want here, it needn't be one of the columns returned by the cte. In fact, a common use-case here is that "foo, bar" are the group identifier and "baz" is some sort of time stamp. In order to keep the latest, you'd do ORDER BY baz desc)

Delete duplicate records leaving only the latest one

You can use Count with partition by to find and insert the duplicate records into answersArchive table like following.

1- Find Duplicate and Insert into answersArchive table

--copy the duplicate records
;WITH cte 
     AS (SELECT id, 
                answer, 
                country_id, 
                question_id, 
                updated, 
                Count(*) 
                  OVER( 
                    partition BY question_id ) ct 
         FROM   answers 
         WHERE  country_id = 15) 
INSERT INTO answersarchive 
SELECT id, 
       answer, 
       country_id, 
       question_id, 
       updated 
FROM   cte 
WHERE  ct > 1 --Give you duplicate records 

2- Delete all duplicates except the latest one.

You can use CTE to delete the records. To find the duplicate records you can use ROW_NUMBER() with PARTITION BY question_id like following query.

;WITH cte 
     AS (SELECT id, 
                answer, 
                country_id, 
                question_id, 
                updated, 
                Row_number() 
                  OVER( 
                    partition BY question_id 
                    ORDER BY updated DESC) RN 
         FROM   answers 
         WHERE  country_id = 15) 

DELETE FROM cte 
WHERE  rn > 1 

How to delete duplicate records in SQL?

You can delete duplicates using i.e. ROW_NUMBER():

with duplicates as
(
    select
        *
        ,ROW_NUMBER() OVER (PARTITION BY FirstName, LastName, age ORDER BY FirstName) AS number
    from yourTable
)
delete 
from duplicates
where number > 1

Each row where number is bigger than 1 is a duplicate.

How can I remove duplicate rows?

Assuming no nulls, you GROUP BY the unique columns, and SELECT the MIN (or MAX) RowId as the row to keep. Then, just delete everything that didn't have a row id:

DELETE FROM MyTable
LEFT OUTER JOIN (
   SELECT MIN(RowId) as RowId, Col1, Col2, Col3 
   FROM MyTable 
   GROUP BY Col1, Col2, Col3
) as KeepRows ON
   MyTable.RowId = KeepRows.RowId
WHERE
   KeepRows.RowId IS NULL

In case you have a GUID instead of an integer, you can replace

MIN(RowId)

with

CONVERT(uniqueidentifier, MIN(CONVERT(char(36), MyGuidColumn)))

Removing duplicate rows from table in Oracle

Use the rowid pseudocolumn.

DELETE FROM your_table
WHERE rowid not in
(SELECT MIN(rowid)
FROM your_table
GROUP BY column1, column2, column3);

Where column1, column2, and column3 make up the identifying key for each record. You might list all your columns.

How do I delete all the duplicate records in a MySQL table without temp tables

Add Unique Index on your table:

ALTER IGNORE TABLE `TableA`   
ADD UNIQUE INDEX (`member_id`, `quiz_num`, `question_num`, `answer_num`);

Another way to do this would be:

Add primary key in your table then you can easily remove duplicates from your table using the following query:

DELETE FROM member  
WHERE id IN (SELECT * 
             FROM (SELECT id FROM member 
                   GROUP BY member_id, quiz_num, question_num, answer_num HAVING (COUNT(*) > 1)
                  ) AS A
            );

---

Related Topics