Re: Convert complex delete sql statement to Oracle?

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:273215737113

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:3092495541617   

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