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: mjohnson <crvmp3_at_hotmail.com>
Date: 5 Feb 2007 20:43:43 -0800
Message-ID: <1170737023.744522.149540@v45g2000cwv.googlegroups.com>


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. Received on Mon Feb 05 2007 - 22:43:43 CST

Original text of this message

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