or.. an even cuter syntax!
delete from t1 where (col1, col2) in (select col1, col2 from t2);
"Niall Litchfield" <niall.litchfield_at_dial.pipex.com> wrote in message
news:3f17052d$0$15037$cc9e4d1f_at_news.dial.pipex.com...
> "Thomas Kellerer" <spam_eater_at_gmx.net> wrote in message
> news:bf6f1v$beoqf$1_at_ID-13919.news.uni-berlin.de...
> > Niall Litchfield wrote:
> > >
> > > SQL> delete t1
> > > 2 where exists (
> > > 3 select null from t2
> > > 4 where t1.id=t2.id
> > > 5 and t1.col2=t2.col2);
> > >
> >
> > I was just about to post an answer with that version of the delete
> > statement as well :-)
> >
> > I would think that this is more efficient then having two subselects.
>
> I *believe* that deleting from a view would be yet more efficient, but
> implies that the base tables need primary keys.
>
>
> --
> Niall Litchfield
> Oracle DBA
> Audit Commission UK
> *****************************************
> Please include version and platform
> and SQL where applicable
> It makes life easier and increases the
> likelihood of a good answer
> ******************************************
>
>
Received on Fri Jul 18 2003 - 12:41:23 CDT