Re: question on delete statement

From: Daniel Morgan <damorgan_at_exxesolutions.com>
Date: Fri, 20 Jun 2003 15:07:53 -0700
Message-ID: <3EF385B9.77FA55F6@exxesolutions.com>

Anurag Varma wrote:

> "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

I'd love to humor you but I won't ... I was reacting to:

"maintab = (item, price) with PK on item
> and
> localtab = (item, lprice)"

and if you can get that to work ... go for it.

--
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)

Received on Fri Jun 20 2003 - 17:07:53 CDT