Re: UPDATE query problem

On Jun 30, 12:33 pm, "Syltrem" <syltremz...@xxxxxxxxxxxx> wrote:
<sybra...@xxxxxxxxx> a écrit dans le message de news:
p0sc8397cpl4r0fipgrbk2ti20khb3j...@xxxxxxxxxx





On Sat, 30 Jun 2007 16:31:54 +0200, Urs Metzger <u...@xxxxxxxxxxxxx>
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?


.