Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: UPDATE query problem

Re: UPDATE query problem

From: Syltrem <syltremzulu_at_videotron.ca>
Date: Fri, 29 Jun 2007 22:04:57 -0400
Message-ID: <138beihb25gl43@corp.supernews.com>

"hpuxrac" <johnbhurley_at_sbcglobal.net> a écrit dans le message de news: 1183166187.876588.214360_at_o61g2000hsh.googlegroups.com...

>
> update table mytable set col1 = ( select query1 that you need ), col2
> = ( select query2 that you need ) where mytable.keyval = ( select
> query3 that you need )
>
> Are you getting an error message with syntax like that above?
>
> What exactly do you have for SQL when you attempt to do it all in 1
> query?
>

I was trying to avoid using subqueries twice to get the value for DESCRIPTION and HELPTEXT as they both come from the same row. And also tried to avoid reading the XREF table many times.

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?

Since the selection of rows in MYTABLE is made by querying XREF (in the where clause of the update statement), and I have to re-read XREF again to find the correct value for DESCRIPTION, and that I have to do this as many times as I have columns to update, it seems to me that`s overkill.

Is there a better way

Thanks
Syltrem Received on Fri Jun 29 2007 - 21:04:57 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US