May 20, 2008 at 5:48 am
How to delete duplicate records from a table?
create table customer
(custid int,
custname varchar(20),
refno int)
insert into customer values(1,'myname',1)
insert into customer values(2,'yourname',3)
insert into customer values(3,'name',2)
insert into customer values(1,'myname',1)
select * from customer
1 myname 1
2 yourname 3
3 name 2
1 myname 1
The primary record have to be there. Only the last record to be deleted.
May 20, 2008 at 6:24 am
ALTER Table to add an additional column with IDENTITY Enabled. Drive your logic based on the new column and once the duplicate records or deleted, drop the column.
Prasad Bhogadi
www.inforaise.com
May 20, 2008 at 7:02 am
[font="Verdana"]1. Select the distint records from original table and insert them into temp table.
2. Truncate the original table and insert the al records from Temp table into original table.
3. Then drop the temp table.
Mahesh[/font]
MH-09-AM-8694
May 20, 2008 at 12:09 pm
[font="Arial"]
May I suggest use set rowcount 1...for example:
create table tlb_test
( tname varchar(20) null,
tother int null )
insert tlb_test
select 'this', 1 union all
select 'This', 2 union all
select 'this', 1 union all
select 'nope', 3
select * from tlb_test
select * from tlb_test
where tother = 1
and tname = 'this'
set rowcount 1
go
delete tlb_test
where tother = 1
and tname = 'this'
go
set rowcount 0
go
[/font]
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply