Re: UPDATE query problem

From: Urs Metzger <urs_at_ursmetzger.de>
Date: Sat, 30 Jun 2007 16:31:54 +0200
Message-ID: <f65pgn$on4$1@online.de>

sybrandb_at_hccnet.nl schrieb:
> On Fri, 29 Jun 2007 22:04:57 -0400, "Syltrem"
> <syltremzulu_at_videotron.ca> wrote:
>

>> Update MYTABLE
>> set DESCRIPTION = (select DESCRIPTION from MYTABLE where 
>> MYTABLE.KEYVAL=(select XREF.KEYVAL2 from XREF where 
>> XREF.KEYVAL1=MYTABLE.KEYVAL)),
>> set HELPTEST = (select HELPTEXT from MYTABLE where MYTABLE.KEYVAL=(select 
>> XREF.KEYVAL2 from XREF where XREF.KEYVAL1=MYTABLE.KEYVAL))
>> where MYTABLE.KEYVAL=XREF.KEYVAL1
>>
>> (something like this I think, I would have to find good alias names to get 
>> it to work)
>>
>> What if I had 20 columns to update like this, I need to have 20 subqueries, 
>> each reading the exact same row? Is there a more efficient way of doing 
>> this?

>
> The correct syntax for this UPDATE statement =
> update mytable
> set (description, helptext ) =
> (select description,helptext
> from mytable
> where mytable.keyval in (select xref.keyval2 from xref where
> xref.keyval1 = mytable.keyval))
>
> I see only 1 subquery.
>
> If you have bad design, then fix the design.
> Other than, maybe you can use the WITH statement introduced in 9i.
>
> Do NOT convert this into PL/SQL: whatever you do, you CAN do it use
> SQL, and using SQL won't involve context switches, so you should do it
> in SQL.
>

Sorry, Sybrand, your update performs fine, but with wrong results. It tries to find value pairs in xref where keyval1 = keyval2. 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.

For correct reults, try:

update mytable a

    set (a.description, a.helptext) = (select b.description, b.helptext

                                         from mytable b, xref
                                        where b.keyval = xref.keyval2
                                          and xref.keyval1 = a.keyval);

Urs Metzger Received on Sat Jun 30 2007 - 09:31:54 CDT