Deleting records SQL

[PerlOfWisdom]

Re: surely....

The query part of this has a left outer join, so it includes all records from tblThis and just the matching records from tblThat.

Where there is no match, the query returns a null.

It therefore selects the records which are NOT IN tblThat.


The top line deletes these records from tblThis.

This is not standard SQL, however and wouldn't work in Oracle or MySQL.

[nigelspencer]

from the Oracle 9i tech article

not an Oracle bloke myself Perl (Sql server/Sybase for many years) however this is a line from a tech article on oracle describing it's attempts to comply qith SQL99, hence it would seem that Oracle think it should work. Seem to remember I picked the original code up from one of Joe Celkos articles

*To write a query that performs an outer join of tables A and B and returns all rows from A (a left outer join), use the ANSI LEFT [OUTER] JOIN syntax. For all rows in A that have no matching rows in B, Oracle returns null for any select list expressions containing columns of B*

technet.oracle.com/produc...jan14.html

[PerlOfWisdom]

Re: from the Oracle 9i tech article

Yes, that bit works, but you can't then use that as part of a delete.