Answer / victor

this can help if you want to keep only different records

create table test
(
id int,
name varchar(20)
)

insert into test VALUES(1,'test')
insert into test VALUES(2,'test')
insert into test VALUES(2,'test')
insert into test VALUES(3,'test')
insert into test VALUES(4,'test')
insert into test VALUES(5,'test')
insert into test VALUES(6,'test')
insert into test VALUES(7,'test')
insert into test VALUES(7,'test')
insert into test VALUES(7,'test')



select * from test order by 1

while @@rowcount != 0
begin
delete top (1) test where id in
(
select id
FROM test
GROUP BY id having count(id)>1
)
end

select * from test order by 1


-- At the end you will have only differents IDS

Answer / onlymoid

CREATE TABLE dbo.duplicateTest ------Deleting
Duplicates with same id
(
[ID] [int] ,
[FirstName] [varchar](25),
[LastName] [varchar](25)
) ON [PRIMARY]

INSERT INTO dbo.duplicateTest VALUES(1, 'Bob','Smith')
INSERT INTO dbo.duplicateTest VALUES(2, 'Dave','Jones')
INSERT INTO dbo.duplicateTest VALUES(3, 'Karen','White')
INSERT INTO dbo.duplicateTest VALUES(1, 'Bob','Smith')



SELECT * FROM dbo.duplicateTest

SET ROWCOUNT 1
DELETE FROM dbo.duplicateTest WHERE ID = 1
SET ROWCOUNT 0

SELECT * FROM dbo.duplicateTest

Drop table dbo.duplicatetest

Answer / smitha

;with empctc(empid,ename,sal,deptid,ranking)
as
(Select empid,ename,sal,deptid,ranking=Dense_rank() over (
partition by empid,ename,sal,deptid order by NEWID() asc)
from emp
)
delete * from empctc where ranking>1

Answer / manjeet kumar

delete from table_name where column_name='value' and rowid
not in (select max(rowid) from table_name where
column_name='value');

e.g. create table duplicate (name varchar(15), rollno number
(10));

insert into duplicate (name,rollno) values ('mkumar',2);
insert into duplicate (name,rollno) values ('mkumar',2);

delete from duplicate where name='mkumar' and rowid not in
(select max(rowid) from duplicate where name='mkumar');

Answer / debasish

while @@rowcount != 0
begin
delete top (1) test where columnname in
(
select columnname
FROM tablename
GROUP BY columnname having count(*)>1
)
end

Answer / eashwar v

IN SQL SERVER 2005, This can be easily achieved without
crating unique identifier by using CTE and ROW_NUMBER (),
the modified query for sql server 2005 goes here
***********************************************
WITH T1 AS (SELECT ROW_NUMBER ( ) OVER ( PARTITION BY ID,
FNAME, LNAME ORDER BY ID ) AS RNUM FROM DUPLICATE )
DELETE FROM T1 WHERE RNUM > 1
***********************************************
To get a detail grasp on ROW_NUMBER () OVER () … Refer MSDN
http://msdn2.microsoft.com/en-us/library/ms186734.aspx for.

Answer / sanjay kumar dinda

Deletion of Rows depends upon the different condition...
Consider based on n columns the entire recordset is
treating a duplicate... We can delete the duplicate by
using following method...

Add one column and set uniue values to that column..

ALTER TABLE TABLE1 ADD ID INT IDENTITY(1,1)

COnsider T contains 40 columns and based on C1,C2,C3 we
have to find the duplicate and we have to delete the same...


Delete T1

FROM TABLE1 T1,TABLE1 T2
WHETE T1.C1=T2.C1 AND
T1.C2=T2.C2 AND
T1.C3=T2.C2
AND T1.ID>T2.ID


I think this will help....

Answer / vignesh chandrasekaran

select Distinct column name from Table Name

Answer / vineet dhamija

the best approach i came across in simple form too
just create a temporary table with the distinct values and
truncate this table than copy the values back and u r good to go
select distinct * into #temp from urtable
truncate table urtable
insert into urtable select * from #temp
drop table #temp

Answer / gerry

delete from my_table where my_primary_key in
(select a.primary_key from my_table a, my_table b
where not a.my_primary_key = b.my_primary_key
and [insert restriction that makes the 2 rows the same]
)

How is table type constraint applied to a table?

0 Answers  

What is the difference between MVC and Teir Architecher? Plz explain with Layyered Programming example...? Thanks

0 Answers   TCS,

How to generate create function script on an existing function?

0 Answers  

wat wil hapn if we give the both read and deny read permission to user?

0 Answers   IBM,

When multiple after triggers are attached to sql table, how to control the order of execution?

0 Answers  

What is the purpose of optimization?

0 Answers  

What are window functions in sql server?

0 Answers  

I applied Transactional with updatable subscriptions replication on 2 tables now i want to delete those 2 tables but i cannot delete those tables as replication is running how can i stop replication for those 2 tables(but i don't want to delete those replicated tables but i need to stop the replication) how can i do that

0 Answers  

How to download and install microsoft .net framework version 2.0?

0 Answers  

Is it true that rules do not apply to data already existing in a database at the time the rule is created?

0 Answers  

What is Trigger?

3 Answers   Misys,

Can a stored procedure call itself or recursive stored procedure? How much level sp nesting is possible?

0 Answers