asked on 7/22/2008

script to delete duplicate records in a table

I have a table that has duplicate records in ti and I need to delete them.

I would like a script that can do this

duplicate fields to use
doc_ref1
acct_name
amt1
doc_datei1
doc_type
The table name is glf_ldg_acc_trans

Microsoft SQL Server Microsoft SQL Server 2005

Last Comment

Matthew34

7/22/2008

momi_sabag

7/22/2008

try this

with t1 as
(
select row_number() over (partition by doc_ref1,acct_name,amt1,doc_datei1,doc_type order by (select 0)) as rown,
doc_ref1,
acct_name,
amt1,
doc_datei1,
doc_type
from
glf_ldg_acc_trans
)
delete from t1 where rown > 1

note that this will delete all duplicate records leaving you with one copy of each duplicate
if you have many duplicate records you might consider breaking this into multiple statements

ASKER CERTIFIED SOLUTION

EugeneZ

7/22/2008

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

Matthew34

7/22/2008

ASKER

When I run this script I get these errors
Server: Msg 195, Level 15, State 10, Line 1
'row_number' is not a recognized function name.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near ')'.

select row_number() over (partition by doc_ref1,acct_name,amt1,doc_datei1,doc_type order by (select 0)) as rown,
doc_ref1,
acct_name,
amt1,
doc_datei1,
doc_type
from
glf_ldg_acc_trans