View previous topic :: View next topic
|
Author |
Message |
srinivas.u
New User
Joined: 07 Jun 2004 Posts: 19
|
|
|
|
hi
i have 100 record in table but i am not declared in primary key
how to delete duplicate rows in table plz send to mail |
|
Back to top |
|
|
jz1b0c
Active User
Joined: 25 Jan 2004 Posts: 160 Location: Toronto, Canada
|
|
|
|
srinivas.u wrote: |
hi
i have 100 record in table but i am not declared in primary key
how to delete duplicate rows in table plz send to mail |
based on some key or clumns you must be identifying them as duplicates
did you identify those columns? |
|
Back to top |
|
|
ovreddy
Active User
Joined: 06 Dec 2004 Posts: 211 Location: Keane Inc., Minneapolis USA.
|
|
|
|
Hi Srini,
You can delete all duplicate records by using the following query... but as I think it will delete all duplicates inclusing original... check out..
delete from table a where 1!=(select count(*) from table b where b.col1=a.col1 and b.col2=a.col2 and b.col3=a.col3 and ......)
Bye,
Reddy. |
|
Back to top |
|
|
Hames
New User
Joined: 03 Oct 2005 Posts: 49
|
|
|
|
Hi Srinivas,
Code: |
DELETE
FROM TABLE A
WHERE (A.COL1,A.COL2,A.COL3)
IN (SELECT B.COL1,B.COL2,B.COL3
FROM TABLE B
WHERE A.COL1 = B.COL1
AND A.COL2 = B.COL2
AND A.COL3 = B.COL3
GROUP BY B.COL1, B.COL2, B.COL3
HAVING COUNT(*) > 1);
|
You can try this query also. |
|
Back to top |
|
|
|