Follow Our FB Page << CircleMedia.in >> for Daily Laughter. We Post Funny, Viral, Comedy Videos, Memes, Vines...


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

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



More SQL Server Interview Questions

List some advantages and disadvantages of stored procedure?

0 Answers  


What is a domain constraint give an example?

0 Answers  


What is reference section?

0 Answers  


what is mean by crystal repoart? ahere we will mainly use that?

1 Answers  


What are the purposes of floor and sign functions?

0 Answers  






What is serializable?

0 Answers  


How to sort the query output with order by clauses in ms sql server?

0 Answers  


Why it is recommended to avoid referencing a floating point column in the where clause?

0 Answers  


What is best institute to Learn DotNET And SQL in chennai?

0 Answers  


Can anyone explain difference between Database, Data warehouse and Data mart with some example?````

4 Answers  


How to list all login names on the ms sql server?

0 Answers  


Explain table valued parameters in sql server? Why tvp used?

0 Answers  






Categories