Re: UPDATE query problem

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.

-- 
 
Sybrand Bakker
Senior Oracle DBA