Re: UPDATE query problem

On Fri, 29 Jun 2007 22:04:57 -0400, "Syltrem"
<syltremzulu@xxxxxxxxxxxx> 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
.

Relevant Pages

  • Re: UPDATE query problem
    ... What if I had 20 columns to update like this, I need to have 20 subqueries, each reading the exact same row? ... update mytable set (description, helptext) = ... SQL, and using SQL won't involve context switches, so you should do it ...
    (comp.databases.oracle.server)
  • Re: UPDATE query problem
    ... DESCRIPTION and HELPTEXT as they both come from the same row. ... And also tried to avoid reading the XREF table many times. ... set HELPTEST = (select HELPTEXT from MYTABLE where MYTABLE.KEYVAL=(select ...
    (comp.databases.oracle.server)