MVSFORUMS.com

eskaysem · Beginner Joined: 31 Mar 2003 Posts: 29 Topics: 13

Hi,
Suppose I have one table T1, with no primary Keys defined.

Sno. Ano.
_______________

S1 A1
S1 A1
S2 A2
S2 A2
S2 A2
S1 A1
S3 A3

Now I have an option to run only one query...and my Output should be affeted on that physical Tbale and my o/p should be:

Sno. Ano.
________________

S1 A1
S2 A2
S3 A3

That means all the duplicates shuold be removed keeping one copy.

I need to do this in only one query.
_________________
Thanks!,
Santosh Kulkarni

kolusu · Posted: Mon Mar 31, 2003 2:23 pm Post subject:

Santhosh,

eskaysem · Beginner Joined: 31 Mar 2003 Posts: 29 Topics: 13

This will only select the Distinct rows. My requirement is to delete the rest of rows which are found to be duplicates.
_________________
Thanks!,
Santosh Kulkarni

Himesh · Posted: Sat Apr 12, 2003 12:11 pm Post subject:

Santosh,

You will not be able to achieve the desired result using "only" one query.

Why don't you unload the data using the query that Kolusu had suggested,
and then load the same back into the table with the REPLACE option, which will delete all the existing rows.

In this way you will still be using only one query....

regards,
Himesh

hari_uss · Posted: Sun Apr 13, 2003 4:33 am Post subject:

In Oracle,