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

Home -> Community -> Usenet -> c.d.o.misc -> Re: Need help with update syntax using subquery

Re: Need help with update syntax using subquery

From: DA Morgan <damorgan_at_psoug.org>
Date: Tue, 06 Feb 2007 08:23:07 -0800
Message-ID: <1170778986.135186@bubbleator.drizzle.com>


mjohnson wrote:

> On Feb 5, 5:53 pm, "Mark D Powell" <Mark.Pow..._at_eds.com> wrote:

>> On Feb 5, 5:24 pm, "mjohnson" <crv..._at_hotmail.com> wrote:
>>
>>
>>
>>
>>
>>> I'm new to PL/SQL and have a TSQL (MSSQL) background. Typically, if I
>>> needed to do an update using a subquery I would do this:
>>> update mytable set t1.col=t2.col from mytable t1 inner join
>>> othertable t2 on t1.ID=t2.ID
>>> but that doesn't work in PL/SQL. But this seems to work:
>>> update mytable t1 set (t1.col)=(select col from othertable where
>>> ID=t1.ID)
>>> Is that the analogous syntax. It seems that this update is taking
>>> forever -- I would have expected it to return in a matter of seconds
>>> if not subsecond but it's been 20 minutes and it still hasn't
>>> completed and there are only 22K records. Is there a better way to do
>>> this?
>>> thanks for your time!
>> You need a where clause condition on your update statement as you toldOracleto update every row in the table even if there is no match in
>> the coordinated subquery in which case you just set the column to
>> NULL.
>>
>> For your query to be efficient you should have an index on
>> othertable.id
>> Both tables should have current statistics on them.
>> If you add an index update the statistics
>>
>> General form
>>
>> update table a
>> set colx = ( select col_value from table b where b,col = a.col )
>> where exists ( select 'X' from table b where b.col = a.col )
>>
>> The where clause restricts the set to operating on rows that have a
>> matching row in table b.
>>
>> Often where an exists clause is used in a statement you can substitute
>> an IN clause, that is, where col in ( select....
>>
>> In this case I think the exists, with the index, and current
>> statistics should work fine.
>>
>> Rollback to undo the damage.
>>
>> HTH -- Mark D Powell --- Hide quoted text -
>>
>> - Show quoted text -
> 
> Thanks for the response.  I actually do want to update every row in
> this particular case.  I think my problem is with the index on the
> second table - I'm guessing it doesn't have one. I'm just really
> surprise that it would take as long as it did for such a small table.

You may want to update every row ... but not with a Cartesian join.

There are demos of update code in Morgan's Library at www.psoug.org Scroll down to UPDATE STATEMENTS.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
Received on Tue Feb 06 2007 - 10:23:07 CST

Original text of this message

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