asked on

Deleting Dulpicate row

Dear All,
Is there Any query ,Which will Delete Duplicate Rows from the table which does not have primary key..please
Help me out

whbram

What database are you using?

ASKER CERTIFIED SOLUTION

kdg2000

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

kretzschmar

maybe also

step one: create a table with none duplicate values
create table tmp as Select distinct * from originaltable;

step two: empty sourcetable
delete from originaltable;

step three: store back
insert into originaltable select * from tmp;

step four: drop tmp-table
drop table tmp;

(may not work on all databases)

meikl ;-)

skumaravelan

ASKER

Dear All,
I have tried al the option u all said befoe.My question is,is there any way to do the same with writing a procedure or function

kretzschmar

it would help,
if you let us know the database you have

skumaravelan

ASKER

Hi kretzschmar,
I have oracle 8 as my database.

kdg2000

Oracle 8 is very similar on MS SQL Server. Some finishings are certainly necessary. For example environment variable @@ fetch_status for certain is not present in Oracle, but 100 % there are a similar variable. Anyway standard SQL 92 is uniform both for Oracle and for MS SQL Server.
I think these small finishings as you seem to me can make.

skumaravelan

ASKER

Dear KDq2000,
Thank You very much for ur valuable reply.