Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: SQL Puzzle ...
This one is nice but not perfect
if you have one Record with col1 = 'ABC'
and col2 = col3 = null;
and an other Record with col1 = 'A';col2 = 'B' col3='C'
the SQL statement will delete on of the sets.
The other statement is better but I'm not sure if it works if one of the Columns is null.
If you got problems with that try replacing colx by nvl(colx,'String that doesnt appear in your Table')
Steffen Stellwag <Steffen.Stellwag_at_pcm.bosch.de> wrote in article
<364FFBAD.7E0E_at_pcm.bosch.de>...
> K. Raghuraman wrote:
> >
> > Hi,
> >
> > I have a table with three columns and no keys defined. I want to
> > eliminate only the duplicate records from the table in a single SQL
> > delete statement. How do I construct my SQL statement ?
> >
> > Thanks and Regards,
> >
> > Raghu
>
>
> Hi
>
> placehoder key for better reading my example :
>
> key := col1 ||col2||col3 ;
>
> the delete-statement is as follows :
>
> DELETE FROM TABLE T
>
> where T.ROWID > (SELECT MIN(T.ROWID) FROM TABLE X
> WHERE X.KEY = L.KEY );
>
> Thats it
>
> Steffen
>
Received on Mon Nov 16 1998 - 09:27:38 CST