May 26, 2006 at 8:12 am
Hi
I want to delete the repeated rows.
Ex: There is a student table.
Sno Sname
1 raju
2 kumar
3 mike
4 raju
5 mike
6 michael
In this table I want to delete 2 records which are repeated, after deleted it should contain 4 rows.
Sno sname
1 raju
2 kumar
3 mike
4 michael
Please give me the query.
Regards,
Thirumalaraj.
May 26, 2006 at 1:33 pm
backup the table/db first
delete from student
where sno not in (select min(sno) from test group by sname)
create table transfer(sno int identity, sname varchar(10))
insert into transfer select sname from student
Check to make sure the data that you want is in Transfer
truncate table student
insert into test select * from transfer
drop table transfer
May 30, 2006 at 11:06 am
Another way of getting your required output with my own example.
Run the below query in single batch.
--------------------------------------------------------
create table #ps_t1 (sno int identity(1,1) , sname Varchar(10))
GO
insert into #ps_t1 values ('ra')
insert into #ps_t1 values ('km')
insert into #ps_t1 values ('mk')
insert into #ps_t1 values ('mk')
insert into #ps_t1 values ('km')
insert into #ps_t1 values ('mi')
GO
SELECT distinct sname into #ps_t2 from #ps_t1
Go
truncate table #ps_t1
GO
insert into #ps_t1 select sname from #ps_t2
Go
select * from #ps_t1
------------------------------------------------------
Note:- Truncate table command reset's the identity column property.
------------
Prakash Sawant
http://psawant.blogspot.com
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply