Re: how does this delete work?

On Jul 9, 11:09 am, steph <stepha..._at_yahoo.de> wrote:
> Hi,
>
> Recently I came across this delete statement inside a package that I
> have to debug:
>
> DELETE FROM
> (
> SELECT 1
> FROM KUNDEN_SOLL ks, KUNDEN k
> WHERE ks.ksl_kun_id = k.kun_id
> ...
> )
>
> I must say the syntax is a little uncommon for me. What's the
> advantage of doing things that way instead of issuing a plain "delete
> from table where ..."?
>
> thanks,
> stephan

Stephan, the syntax allows DML, a delete in this case, to be performed on rows of a key preserved table based on the results of a join without defining the join view to the database as a permanent view. Writing the code this way can be more efficient than having to code the requirements as sub-queries.

See the Applications Developers Guide - Fundamentals, ch 2, Managing Schema Objects, section Modifying a Join View for limitations of join views and for information on key preserved tables.

The material in the manual used defined views (create view x) but it is the same concept in use.

HTH -- Mark D Powell -- Received on Mon Jul 09 2007 - 11:34:18 CDT