Re: Need help with update syntax using subquery

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 told Oracle to 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 -- Received on Mon Feb 05 2007 - 17:53:04 CST