Is it possible to delete duplicate rows in a table without using a temporary t

Categories >> Software >> Databases >> SQL Server
Suggest New Category

Is it possible to delete duplicate rows in a table without
using a temporary table ?

Question Posted / guest

12 Answers
25682 Views
TCS, I also Faced
E-Mail Answers

Answers were Sorted based on User's Feedback

Is it possible to delete duplicate rows in a table without using a temporary table ?..

Answer / kumar

sno names
1 arun
2 arun
3 arun
6 arun
7 bala
11 bala
12 guna
9 guna
14 guna
10 raj
13 raj

Table Name T1
Fields Names Sno,Names
Delete From t1 where sno not in
(select min(sno) from t1 group by names)

Is This Answer Correct ?

17 Yes

6 No

Is it possible to delete duplicate rows in a table without using a temporary table ?..

Answer / balaji

yes its very much possible.
create table #student
(name varchar(10),age integer)

insert into #student values ('A',12)
go 2
insert into #student values ('B',13)
go 3
insert into #student values ('C',15)
go 4
insert into #student values ('D',14)
go 5

SET ROWCOUNT 1
DELETE #student FROM #student A WHERE (SELECT COUNT(*) FROM
#student B WHERE A.NAME = B.NAME AND A.AGE = B.AGE) > 1
WHILE @@ROWCOUNT > 0
DELETE #student FROM #student A WHERE (SELECT COUNT(*) FROM
#student B WHERE A.NAME = B.NAME AND A.AGE = B.AGE) > 1
SET ROWCOUNT 0

Is This Answer Correct ?

3 Yes

0 No

Is it possible to delete duplicate rows in a table without using a temporary table ?..

Answer / kalyan.k

yes,it is possible.

By using row_number function.

dept table contains two columns(eid,ename)

eid dname

1 k
1 j
1 u
2 k
2 j
2 u

with [dept ordered by eid] as
(
select row_number()over(partition by ei order by eid)as
rowid,* from dept
)
delete from [dept ordered by eid] where rowid >1

Is This Answer Correct ?

4 Yes

2 No

Is it possible to delete duplicate rows in a table without using a temporary table ?..

Answer / brajesh

DECLARE temp CURSOR FOR SELECT id FROM cars1 c GROUP BY
c.id,c.name,c.year
DECLARE @id int
OPEN temp
FETCH next FROM temp
INTO @id

WHILE @@FETCH_STATUS=0
BEGIN
IF EXISTS (SELECT id FROM cars1 GROUP BY cars1.id
HAVING count(*)>1 AND id=@id)
DELETE TOP(SELECT count(*)-1 FROM cars1 WHERE
cars1.id=@id )FROM cars1 WHERE id=@id

FETCH next FROM temp
INTO @id
END

CLOSE temp
DEALLOCATE temp

Is This Answer Correct ?

1 Yes

0 No

Is it possible to delete duplicate rows in a table without using a temporary table ?..

Answer / ambuj tayal

Yes, it is possible by using in memory table variables on
SQL server 2000 and above.

A table variable can be created and all disticnt values can
be inserted into this table and then deleting all rows from
first table you can insert back disticnt rows to back to
original table. Table variable would automatically be
destroyed whenever bacth execution finishes.

Is This Answer Correct ?

3 Yes

3 No

Is it possible to delete duplicate rows in a table without using a temporary table ?..

Answer / nithya

If u no know the ROWID then u can delete the duplicate rows.

Is This Answer Correct ?

0 Yes

0 No

Is it possible to delete duplicate rows in a table without using a temporary table ?..

Answer / ruban

Use ROWID function. It's available only sql server 2005 and
greater

Is This Answer Correct ?

0 Yes

0 No

Is it possible to delete duplicate rows in a table without using a temporary table ?..

Answer / preetpal kapoor

How can we delete duplicate records in a table without
using rowid() function and temporary table?

Is This Answer Correct ?

0 Yes

0 No

Is it possible to delete duplicate rows in a table without using a temporary table ?..

Answer / ghous

using cursor is another option

Is This Answer Correct ?

1 Yes

3 No

Is it possible to delete duplicate rows in a table without using a temporary table ?..

Answer / krishnaraj p n

Can be done in a simple way.

DELETE FROM <TABLE NAME > WHERE <COLUMN NAME >IN
(
SELECT <COLUMN NAME> FROM <TABLE NAME >
GROUP BY <COLUMN NAME>
HAVING COUNT(<COLUMN NAME>) > 1
)

Is This Answer Correct ?

1 Yes

4 No

Post New Answer