Re: UPDATE query problem

On Jun 30, 12:33 pm, "Syltrem" <syltremz..._at_videotron.ca> wrote:
> <sybra..._at_hccnet.nl> a écrit dans le message de news:
> p0sc8397cpl4r0fipgrbk2ti20khb3j..._at_4ax.com...
>
>
>
>
>
> > On Sat, 30 Jun 2007 16:31:54 +0200, Urs Metzger <u..._at_ursmetzger.de>
> > wrote:
>
> >>Sorry, Sybrand, your update performs fine, but with wrong results.
> >>It tries to find value pairs in xref where keyval1 = keyval2.
> > Yeah, this is why I labeled the design as bad. Apart from that it was
> > relatively late, and I can't verify here, as I don't have Oracle on
> > this system
>
> > In
> >>my test case it leaves mytable.description and mytable.helptext
> >>with all NULLs. And it does use two subqueries:
> >> ... = (select description,helptext ...
> >>and
> >> ... in (select xref.keyval2 ...
>
> >>But maybe my definition of "subquery" differs from your's.
>
> > My point was
> > set <column> = (select .. from ...),
> > set <column> = (select .. from ...)
> > was unnecessary
> > as SQL allows
> > (....,...) = (select ...,... from ....)
>
> > No multiple subqueries required, no need to resort to PL/SQL.
>
> > --
> > Sybrand Bakker
> > Senior Oracle DBA
>
> Hi Sybrand
>
> I didn't know I could use this syntax
> set (col1, col1) = (select...)
>
> This answers my question completely ! I was quite sure there must be a way
> of doing this.
>
> Btw this is a one shot deal to convert some data.
>

The syntax update x set ( col1, col2 ) = ( select cola, colb blah ) can be very useful but sometimes you need different select critera against sometimes different tables for col1 and col2.

If the criteria is the same but you express it still separately instead of within parentheses well then the optimizer has the choice of re-writing it eh? Received on Sat Jun 30 2007 - 12:42:39 CDT