Is it possible to delete duplicate rows in a table without
using a temporary table ?
Answers were Sorted based on User's Feedback
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 |
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 |
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 |
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 |
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 |
Answer / nithya
If u no know the ROWID then u can delete the duplicate rows.
Is This Answer Correct ? | 0 Yes | 0 No |
Answer / ruban
Use ROWID function. It's available only sql server 2005 and
greater
Is This Answer Correct ? | 0 Yes | 0 No |
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 |
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 |
What is SCOPE_IDENTITY() in sql
How will you fine tune a stored procedure or what are the steps that should be taken to fine tune or optimize a stored procedure?
Explain acid?
What is the SQL?????????????????????????????
i have a table like this Eno ename 1 a 2 b 3 c i want to display ename and bossname from table hint boss is also an employee
According to you what goes into making the best database administrator? : sql server database administration
What is an example of a foreign key?
What are sql server procedures?
How to Debug a Stored Procedure?
What is the meaning of lock escalation and why/how to stop this? : sql server database administration
What is filtered index?
explain what is raid and what are different types of raid configurations? : Sql server database administration