Re: Convert complex delete sql statement to Oracle?

Thank you! I knew about "in" and "exists" but I had never seen the syntax to do "(mykey, uniquekey )"

"Thomas Kyte" <tkyte_at_oracle.com> wrote in message news:ak00jr01d10_at_drn.newsguy.com...
> In article <b1y89.1237$Bd4.10300_at_dfw-service2.ext.raytheon.com>, "Brad
says...
> >
> >I can do this statement in SQL Server but I can not figure out how to do
it
> >in Oracle. I just want it to delete records in Table1.
> >
> >
> >DELETE A
> >FROM TABLE1 A, TABLE2 B, TABLE3 C
> >WHERE A.MYKEY= B.MYKEY AND A.UNIQUEKEY = B.UNIQUEKEY
> > AND B.MYKEY= C.MYKEY
> > AND C.KEY1 NOT BETWEEN 'A' AND 'Z';
> >
> >
> >
>
> delete from table1 a
> where (mykey, uniquekey ) in ( select b.mykey, b.uniquekey
> from table2 b, table3 c
> where b.mykey = c.mykey
> and c.key1 not between 'A' and 'Z' );
>
>
> A delete "join" in Sqlserver can always be rewritten using an IN like that
(just
> take the join columns from table A and ask for them to be "in" the join
columns
> from the other tables and push the other tables into that subquery).
>
> This standard sql query will work in both of the databases.
>
> When you get to converting the update of a join -- see
>
>

http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:273215737 113
>
> and when are are even thinking about just porting the procedures "as is",
see
>
>

http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:309249554 1617
>
> and avoid those pesky temporary tables whenever possible.
>
> --
> Thomas Kyte (tkyte@oracle.com) http://asktom.oracle.com/
> Expert one on one Oracle, programming techniques and solutions for Oracle.
> http://www.amazon.com/exec/obidos/ASIN/1861004826/
> Opinions are mine and do not necessarily reflect those of Oracle Corp
>
Received on Wed Aug 21 2002 - 10:43:59 CDT