Delete duplicates - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Delete duplicates

Author

Topic

satya068
Posting Yak Master

233 Posts

Posted - 2010-06-14 : 04:26:06

Hi..
in my table there are duplicates records,from this i would like to delete 2 rows out of 3,

if there any deff between row_count or inserts or updates i know how to delete but in this case could you pls tell me how to delete the 2 unwanted rows.

TABLE_NAME ROW_COUNT INSERTS UPDATES SYS_DATE
EMP_DETAILS 1586240 23 1586240 2010-06-13
EMP_DETAILS 1586240 23 1586240 2010-06-13
EMP_DETAILS 1586240 23 1586240 2010-06-13

thanx

Satya

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-06-14 : 04:30:08

DELETE f
FROM (
SELECT ROW_NUMBER() OVER (PARTITION BY TABLE_NAME, ROW_COUNT, INSERTS, UPDATES, SYS_DATE ORDER BY SYSDATE) AS recID
) AS f
WHERE recID > 1

N 56°04'39.26"
E 12°55'05.63"

satya068
Posting Yak Master

233 Posts

Posted - 2010-06-14 : 04:37:27

thanx fro ur reply peso,
in mytable there is no columnname like ROW_NUMBER
and u mentioned 'f' in the script it should be replace with actual table name right?

thanx

Satya

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-06-14 : 04:51:32

If you use version 2005 or above,just use

DELETE f
FROM (
SELECT ROW_NUMBER() OVER (PARTITION BY TABLE_NAME, ROW_COUNT, INSERTS, UPDATES, SYS_DATE ORDER BY SYSDATE) AS recID FROM your_table
) AS f
WHERE recID > 1

Replace your_table with your actual table

Madhivanan

Failing to plan is Planning to fail

satya068
Posting Yak Master

233 Posts

Posted - 2010-06-14 : 05:45:42

Thanx madhivanan,

i am able to clear duplicates on 2010-06-13,
and the same issue on some of the previous dates aswell in my table,can i know how to clear the duplicates on previous dates pls.

thanx

Satya

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-06-14 : 05:54:56

The above query removes all duplicates regardless of date.
If the date is not part of your key, remove sys_date from the partition clause.

N 56°04'39.26"
E 12°55'05.63"

satya068
Posting Yak Master

233 Posts

Posted - 2010-06-14 : 06:10:06

when i run this script

select * from
(
select table_name, run_date, count(*) as cnt
from EMP_DETAILS_TOTALRECORDS
group by table_name, run_date
) inq
where cnt > 1
order by table_name, run_date

EMP_PAS_ADM_REASON 2010-05-25 00:00:00.000 2
EMP_PAS_ADM_SOURCE 2010-05-25 00:00:00.000 2
EMP_PAS_ADM_TYPE 2010-05-25 00:00:00.000 2
EMP_PAS_ADMISSIONS 2010-05-25 00:00:00.000 2
EMP_PAS_ADMISSIONS1 2010-05-25 00:00:00.000 2
EMP_PAS_ALERT_CODE 2010-06-11 00:00:00.000 2
EMP_PAS_ALERT_CODES 2010-06-11 00:00:00.000 3

there are still showing duplicates for the above tables,but manages to clear duplicates on 2010-06-13.

last column '2' n '3' indicates no_of duplicates in each table on that perticular date.

Satya

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-06-14 : 06:44:14

Are TABLE_NAME and RUN_DATE your key columns?

DELETE f
FROM (
SELECT ROW_NUMBER() OVER (PARTITION BY TABLE_NAME, SYS_DATE ORDER BY SYSDATE DESC) AS recID
FROM EMP_DETAILS_TOTALRECORDS
) AS f
WHERE recID > 1

N 56°04'39.26"
E 12°55'05.63"

satya068
Posting Yak Master

233 Posts

Posted - 2010-06-14 : 10:32:47

thanx for ur reply peso,
actually i need to delete those manually based on no_of_updates becase no_of_updates are not duplicates in my table, the above script is used to delete any duplicates in all the columns.

anyway..sorted now...thanx

Satya

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources