SQL Server 2008: Delete Duplicate Rows

SQL Server 2008: delete duplicate rows

You can DELETE from a cte:

WITH cte AS (SELECT *,ROW_NUMBER() OVER(PARTITION BY uniqueid ORDER BY col2)'RowRank'
             FROM Table)
DELETE FROM cte 
WHERE RowRank > 1

The ROW_NUMBER() function assigns a number to each row. PARTITION BY is used to start the numbering over for each item in that group, in this case each value of uniqueid will start numbering at 1 and go up from there. ORDER BY determines which order the numbers go in. Since each uniqueid gets numbered starting at 1, any record with a ROW_NUMBER() greater than 1 has a duplicate uniqueid

To get an understanding of how the ROW_NUMBER() function works, just try it out:

SELECT *,ROW_NUMBER() OVER(PARTITION BY uniqueid ORDER BY col2)'RowRank'
FROM Table
ORDER BY uniqueid

You can adjust the logic of the ROW_NUMBER() function to adjust which record you'll keep or remove.

For instance, perhaps you'd like to do this in multiple steps, first deleting records with the same last name but different first names, you could add last name to the PARTITION BY:

WITH cte AS (SELECT *,ROW_NUMBER() OVER(PARTITION BY uniqueid, col3 ORDER BY col2)'RowRank'
             FROM Table)
DELETE FROM cte 
WHERE RowRank > 1

How to delete duplicate rows in SQL Server 2008?

Add a primary key. Seriously, every table should have one. It can be an identity and you can ignore it, but make sure that every single table has a primary key defined.

Imagine that you have a table like:

create table T (
    id int identity,
    colA varchar(30) not null,
    colB varchar(30) not null
)

Then you can say something like:

delete T
from T t1
where exists
(select null from T t2
where t2.colA = t1.colA
and t2.colB = t1.colB
and t2.id <> t1.id)

Another trick is to select out the distinct records with the minimum id, and keep those:

delete T
where id not in
(select min(id) from T
group by colA, colB)

(Sorry, I haven't tested these, but one of these ideas could lead you to your solution.)

Note that if you don't have a primary key, the only other way to do this is to leverage a pseudo-column like ROWID -- but I'm not sure if SQL Server 2008 offers that idea.

SQL Server 2008 R2: Delete duplicate rows from multiple tables and keep original one

This query will delete it (emp1 > emp2 ... > emp5):

Declare @sql nvarchar(max) = ''

Select @sql = coalesce(@sql, '')+ '
Delete d From ['+c2.TableName+'] as d
    Inner join ['+c1.TableName+'] as c on c.Emp_FName = d.Emp_FName and c.Emp_LName = d.Emp_LName; 
    '
From Container as c1
Inner Join Container as c2 On c2.TableName > c1.TableName
Order By c1.TableName, c2.TableName

Print @sql
EXEC sp_executesql @sql

However, I think you should take some time to think about your system and data model and try to find a better way of doing it without using dynamic queries.

Delete duplicate entries from Database in SQL server 2008

Try this one -

DECLARE @temp TABLE
(
      ID INT IDENTITY(1,1)
    , Name VARCHAR(10)
    , Title VARCHAR(10)
)

INSERT INTO @temp (Name, Title)
VALUES 
    ('XYZ', 'Manager'),
    ('ABC', 'CEO'),
    ('LMP', 'Clerk'),
    ('XYZ', 'Manager'),
    ('XYZ', 'Manager')

DELETE FROM t
FROM (
    SELECT 
          Name
        , Title
        , rn = ROW_NUMBER() OVER (PARTITION BY Name, Title ORDER BY 1/0) 
    FROM @temp
) t
WHERE rn > 1

SELECT * 
FROM @temp

Query cost -

Output -

ID          Name       Title
----------- ---------- ----------
2           ABC        CEO
3           LMP        Clerk
4           XYZ        Manager

delete specific duplicate records sql server 2008

try this:

With mad(record, minDat) as 
   (Select record, min(addDate)
    From myTable
    group by record)
Delete t
from mytable t join mad m 
   on m.record = t.Record
where t.adddate not in 
   (m.minDat, dateadd(day, 45, m.minDat))

problem is you have 13 records in the source data for record 22344223 that are all the same.

If you only want one copy of these 13 duplicates, then, after deleting the records,

create table dbo.temp (record integer, addDate date)
Insert dbo.temp(record, addDate)
Select distinct record, addDate
from mytable
-- ------------------------
Drop table myTable
-- ------------------------
exec sp_Rename 'dbo.temp', 'dbo.mytable'   

How to delete duplicate cell in a SQL Server 2008 table

Use ROW_NUMBER()

;withe cte1 
as
(
select *,Row_Number() Over (PARTITION BY PhoneNumber ORDER BY Id) as rn from Table_Name
)
select * from cte1 where rn=1;

---

Related Topics