if 3 duplicate records in a table,i want to delete 2 duplicate
records by keeping 1 duplicate and 1 original as it is,how?
Answers were Sorted based on User's Feedback
Answer / mohamed zunu
create table sampletbl(id int,name varchar(100))
insert into sampletbl values(1,'aa')
insert into sampletbl values(1,'aa')
insert into sampletbl values(1,'aa')
with cte as(
select ROW_NUMBER() over (partition by id order by id) as r_no,* from sampletbl)
delete from cte where r_no>1
select * from sampletbl
Is This Answer Correct ? | 9 Yes | 1 No |
Answer / siva raman
By using Rank function we can delete duplicate records in
tables.
Is This Answer Correct ? | 6 Yes | 3 No |
Answer / amol maske
DELETE FROM tablename WHERE ROWID NOT IN(
SELECT MIN(ROWID)FROM tablename GROUP BY columnname);
Is This Answer Correct ? | 5 Yes | 4 No |
Answer / dhananjay
The simplest way to eliminate the duplicate records is to
SELECT DISTINCT into a temporary table, truncate the
original table and SELECT the records back into the original
table. That query looks like this:
select distinct *
into #holding
from dup_authors
truncate table dup_authors
insert dup_authors
select *
from #holding
drop table #holding
Is This Answer Correct ? | 0 Yes | 0 No |
Answer / arun ashok
STEP 1 : Insert distinct value in the one new table.
STEP 2 : Delete the values from existing table.
STEP 3 : Again insert the values from new table to existing
table.
Is This Answer Correct ? | 0 Yes | 1 No |
what is performance tunning in sql server ? explain.
can any one please send sql quries most used in applications.
What are the types of user defined functions in sql server?
what is hash table
What do you mean by an execution plan?
Explain activity monitors
How to set a database state to offline in ms sql server?
how to use DTS package in 2000,2005,2008 in sql server
What is multi-statement table-value user-defined function?
I am learning Testing, so i want to learn SQL also because SQL is important for Testing. I want to know which is best Institute in Ameerpet or SR Nagar or any other place in Hyd? Please help me.
What is explicit cursors?
Give an example of SQL injection attack ?