Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: question on delete statement

Re: question on delete statement

From: Anurag Varma <avarmadba.skipthis_at_yahoo.com>
Date: Fri, 20 Jun 2003 21:42:44 GMT
Message-ID: <odLIa.3171$hI1.435@nwrddc01.gnilink.net>

"Daniel Morgan" <damorgan_at_exxesolutions.com> wrote in message news:3EF37CE6.1E82AEB9_at_exxesolutions.com...
> Anurag Varma wrote:
>
> > "Daniel Morgan" <damorgan_at_exxesolutions.com> wrote in message
> > news:3EF36687.48564458_at_exxesolutions.com...
> > --snip--
> > >
> > > Look at the standard syntax for updating tables with nested tables and
> > objects.
> > > Update and Delete against a SELECT is the norm.
> > >
> > > --
> > > Daniel Morgan
> > > http://www.outreach.washington.edu/extinfo/certprog/oad/oad_crs.asp
> > > damorgan_at_x.washington.edu
> > > (replace 'x' with a 'u' to reply)
> >
> > Daniel,
> >
> > I know deletes based on nested selects are a norm.
> > This thread is referring to the join based delete/update syntax.
> >
> > Tell me .. is this a valid syntax according to you:
> >
> > update (select lprice, price
> > from localtab, maintab
> > where maintab.item = localtab.item)
> > set lprice = price
> > /
> >
> > maintab = (item, price) with PK on item
> > and
> > localtab = (item, lprice)
> >
> > Now I know this can be written as (and this is the norm):
> > update localtab set lprice = (select price from maintab
> > where maintab.item =
> > localtab.item)
> > /
> >
> > Anurag
>
> By norm do you mean what most developers do or valid syntax. I don't think
> most developers perform bulk binding
> but I would certainly consider it the norm in the work that my teams do.
>
> --
> Daniel Morgan
> http://www.outreach.washington.edu/extinfo/certprog/oad/oad_crs.asp
> damorgan_at_x.washington.edu
> (replace 'x' with a 'u' to reply)
>
>

Yes .. I'm not disagreeing with you on the norm. d

What I'm saying is that your statement to the OP about his syntax being wrong
... WITHOUT asking for details ... is INCORRECT!! His syntax is correct!!! The way he is using it might be wrong. If there is a PK on table A and table B is a multi-value table and assuming that the columns f3 and f4 are not present in both tables .. THEN his statement will work and delete rows from the multi value table B.

You of course are refusing to read the details and I can bet you are not aware of join based deletes
being syntactically correct!
To prove me wrong .. tell me how can you say the OP's delete statement is syntactically wrong
because I can prove that it can work under conditions I describe above.

Anurag Received on Fri Jun 20 2003 - 16:42:44 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US