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.
>
The time needed also depends on the number of records in table a, doesn't it?
If you didn't need to update every record in a, but only those with a relation in b, you could
try this:
begin
for r in (select col, col_value from table_b) loop
update table_a
set colx = r.col_value
where col = r.col;
end loop;
end;
/
Received on Tue Feb 06 2007 - 10:58:50 CST
