Query help

Hi friends,
I want to delete some duplicate rows from table.how can delete rows.
any one solve this problem.

quote:Originally posted by pinky

Hi friends,
I want to delete some duplicate rows from table.how can delete rows.
any one solve this problem.

Assuming you have a PK.

DELETE FROM Your Table
WHERE PK NOT IN(SELECT Min(PK) FROM [Your Table] GROUP BY [All Other Columns])


Roji. P. Thomas
http://toponewithties.blogspot.com

what is that pk refers

Pk Means primary key, does your table has any primary key ??

Chirag

http://chirikworld.blogspot.com/

Also you can have a look at following link

http://sqlteam.com/forums/topic.asp?TOPIC_ID=6256

Chirag

http://chirikworld.blogspot.com/

I used this query but it delets two duplicate values(101) in below table.
DELETE FROM ST a
WHERE EXISTS(SELECT b.SNO,b.SNAME,count(*)
FROM ST b
WHERE b.SNO=a.SNO
AND b.SNAME=a.SNAME
GROUP BY b.SNO,b.SNAME
HAVING count(*) >1);

I want

SNO SNAME
--- -------
100 hgjhg
102 ghgg
101 gjhgh

this type of result and below one is my table (st)

SNO SNAME
--- -------
100 hgjhg
102 ghgg
101 gjhgh
101 gjhgh
any body plz solve this.

quote:Originally posted by Roji. P. Thomas



Assuming you have a PK.

DELETE FROM Your Table
WHERE PK NOT IN(SELECT Min(PK) FROM [Your Table] GROUP BY [All Other Columns])


Roji. P. Thomas
http://toponewithties.blogspot.com

I'd have thought the presence of duplicates indicates the absence of a Primary Key?

In this table
dno dname dloc
1 hr hyd
2 admin hyd
2 admin hyd
3 finance hyd
I want to delete only one row that is 2

I want output like this

dno dname dloc
1 hr hyd
2 admin hyd
3 finance hyd

I want sloution.any body solve this.

Step-1:

Select dno, dname, dloc
into #TmpTable
From yourtable
Group By dno, dname, dloc
Having Count(1) > 1

Step-2:

Delete yourtable
From #TmpTable
Where yourtable.dno = #TmpTable.dno
AND yourtable.dname = #TmpTable.dname
AND yourtable.dloc = #TmpTable.dlock

Step-3:

Insert yourtable
Select dno, dname, dlock
From #TmpTable

Drop Table #TmpTable


Thx,
Mayur.

Thanks patel it's working but this is use full for removing lot of duplicaterows.

Hi All,

How to create temporary tables in oracle give some examples.

Try an Oracle forum - this is Microsoft SQL Server territory.<br /><br />Good luck anyway.[<img src='/community/emoticons/emotion-1.gif' alt='' />]

Hi all,
I want to write MSDBA certification and how to prepare this certification exam any body having idea aboutb this plz tell me.

I am agree with Satya. you can find lot of literature on this topic and one of useful and good one on this site as well.. look at this

http://www.sql-server-performance.com/dv_delete_duplicates.asp

Hi all,

I want to know the SQL DBA roles and activities.

Thanks

pinky

Hi all,
I want know the sql server DBA roles and activities.