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)))

There's a good article on removing duplicates on the Microsoft Support site. It's pretty conservative - they have you do everything in separate steps - but it should work well against large tables.

I've used self-joins to do this in the past, although it could probably be prettied up with a HAVING clause:

DELETE dupes
FROM MyTable dupes, MyTable fullTable
WHERE dupes.dupField = fullTable.dupField 
AND dupes.secondDupField = fullTable.secondDupField 
AND dupes.uniqueField > fullTable.uniqueField

This is the easiest way to delete duplicate record

 DELETE FROM tblemp WHERE id IN 
 (
  SELECT MIN(id) FROM tblemp
   GROUP BY  title HAVING COUNT(id)>1
 )

Here is another good article on removing duplicates.

It discusses why its hard: "SQL is based on relational algebra, and duplicates cannot occur in relational algebra, because duplicates are not allowed in a set."

The temp table solution, and two mysql examples.

In the future are you going to prevent it at a database level, or from an application perspective. I would suggest the database level because your database should be responsible for maintaining referential integrity, developers just will cause problems ;)

Oh sure. Use a temp table. If you want a single, not-very-performant statement that "works" you can go with:

DELETE FROM MyTable WHERE NOT RowID IN
    (SELECT 
        (SELECT TOP 1 RowID FROM MyTable mt2 
        WHERE mt2.Col1 = mt.Col1 
        AND mt2.Col2 = mt.Col2 
        AND mt2.Col3 = mt.Col3) 
    FROM MyTable mt)

Basically, for each row in the table, the sub-select finds the top RowID of all rows that are exactly like the row under consideration. So you end up with a list of RowIDs that represent the "original" non-duplicated rows.

I had a table where I needed to preserve non-duplicate rows. I'm not sure on the speed or efficiency.

DELETE FROM myTable WHERE RowID IN (
  SELECT MIN(RowID) AS IDNo FROM myTable
  GROUP BY Col1, Col2, Col3
  HAVING COUNT(*) = 2 )

1. Create new blank table with the same structure

2. Execute query like this

   INSERT INTO tc_category1
   SELECT *
   FROM tc_category
   GROUP BY category_id, application_id
   HAVING count(*) > 1
   

3. Then execute this query

   INSERT INTO tc_category1
   SELECT *
   FROM tc_category
   GROUP BY category_id, application_id
   HAVING count(*) = 1
   

From the application level (unfortunately). I agree that the proper way to prevent duplication is at the database level through the use of a unique index, but in SQL Server 2005, an index is allowed to be only 900 bytes, and my varchar(2048) field blows that away.

I dunno how well it would perform, but I think you could write a trigger to enforce this, even if you couldn't do it directly with an index. Something like:

-- given a table stories(story_id int not null primary key, story varchar(max) not null)
CREATE TRIGGER prevent_plagiarism 
ON stories 
after INSERT, UPDATE 
AS 
    DECLARE @cnt AS INT 

    SELECT @cnt = Count(*) 
    FROM   stories 
           INNER JOIN inserted 
                   ON ( stories.story = inserted.story 
                        AND stories.story_id != inserted.story_id ) 

    IF @cnt > 0 
      BEGIN 
          RAISERROR('plagiarism detected',16,1) 

          ROLLBACK TRANSACTION 
      END 

Also, varchar(2048) sounds fishy to me (some things in life are 2048 bytes, but it's pretty uncommon); should it really not be varchar(max)?

If all the columns in duplicate rows are same then below query can be used to delete the duplicate records.

SELECT DISTINCT * INTO #TemNewTable FROM #OriginalTable
TRUNCATE TABLE #OriginalTable
INSERT INTO #OriginalTable SELECT * FROM #TemNewTable
DROP TABLE #TemNewTable

For the table structure

MyTable

RowID int not null identity(1,1) primary key,
Col1 varchar(20) not null,
Col2 varchar(2048) not null,
Col3 tinyint not null

The query for removing duplicates:

DELETE t1
FROM MyTable t1
INNER JOIN MyTable t2
WHERE t1.RowID > t2.RowID
  AND t1.Col1 = t2.Col1
  AND t1.Col2=t2.Col2
  AND t1.Col3=t2.Col3;

I am assuming that RowID is kind of auto-increment and rest of the columns have duplicate values.

I think this would be helpfull. Here, ROW_NUMBER() OVER(PARTITION BY res1.Title ORDER BY res1.Id)as num has been used to differentiate the duplicate rows.

delete FROM
(SELECT res1.*,ROW_NUMBER() OVER(PARTITION BY res1.Title ORDER BY res1.Id)as num
 FROM 
(select * from [dbo].[tbl_countries])as res1
)as res2
WHERE res2.num > 1

Other way to remove duplicates based on two columns

I found this query easier to read and replace.

DELETE 
FROM 
 TABLE_NAME 
 WHERE FIRST_COLUMNS 
 IN( 
       SELECT * FROM 
           ( SELECT MIN(FIRST_COLUMNS) 
             FROM TABLE_NAME 
             GROUP BY 
                      FIRST_COLUMNS,
                      SECOND_COLUMNS 
             HAVING COUNT(FIRST_COLUMNS) > 1 
            ) temp 
   )

note: It's good to simulate query before you run it.

First you can select minimum RowId's using MIN() and Group By. We will keep these Rows.

   SELECT MIN(RowId) as RowId
   FROM MyTable 
   GROUP BY Col1, Col2, Col3

And Delete RowId's those are not in selected minimum RowId's using

DELETE FROM MyTable WHERE RowId Not IN()

Final query:

DELETE FROM MyTable WHERE RowId Not IN(

    SELECT MIN(RowId) as RowId
    FROM MyTable 
    GROUP BY Col1, Col2, Col3
)

You can also check my answer in SQL Fiddle

A very simple way to delete duplicate rows of table in postgresql.

DELETE FROM table1 a
USING table1 b
WHERE a.id < b.id
AND a.column1 = b.column1
AND a.column2 = b.column2;

Delete Duplicate record

Greater Than operator in this case delete all record except first record

DELETE u1 FROM users u1 JOIN users u2 WHERE u1.id > u2.id AND u1.email=u2.email

< Less than operator in this case delete all record except last record

DELETE u1 FROM users u1 JOIN users u2 WHERE u1.id < u2.id AND u1.email=u2.email