Given an unnormalized table with columns:Name,Phone number,Address You notice that some records in Name column are duplicate. Write a T-SQL statement to delete all duplicate record under Name column.

  • Interview Candidate
  • 12
  • 3220

Showing Answers 1 - 12 of 12 Answers

Mainak Aich

  • Apr 6th, 2006
 

The query will be :delete from tabname where rowid not in (select max(rowid) from tabname group by name )Here tabname is the table name.

  Was this answer useful?  Yes

rayhan21

  • Apr 20th, 2006
 

Hi

i am rayhan i am not understaning the query itself so please explain the query & plz answer the query also

thank you

rayhan

  Was this answer useful?  Yes

smrati saxena

  • May 5th, 2006
 

Hi...

For deleting the duplicate records from any table, we have to use 1 virtual column called 'ROWID' which remains unique for alll the rows in the table, since they contain the physical address where the row is stored.

Here is the query....

delete from tablename t1 where t1.name =(select max(rowid) from tablename t2 where t1.name=t2.name)

  Was this answer useful?  Yes

deepthi

  • Mar 22nd, 2007
 

hi, we use rowid for duplicates since it is unique but witin the query why u r usin MAX(rowid).plz explain it

  Was this answer useful?  Yes

rroplekar

  • Nov 16th, 2007
 


  I think your query is perfect.

select t.name from table t where t.rowid not in ( select max(t1.rowid) from table t1 group by name)

  Was this answer useful?  Yes

goksn

  • Sep 7th, 2009
 

delete from table-name where Name in (Select Name from table_name group by name having count(name)>1)

  Was this answer useful?  Yes

Give your answer:

If you think the above answer is not correct, Please select a reason and add your answer below.

 

Related Answered Questions

 

Related Open Questions