Re: Update question

> I have a tough SQL question... the issue is performance.
>
> I would like to update two columns of a very large (millions of rows) table
> from the contents
> of a smaller table (100K rows). There is a primary key on each table. I
> tried to do this with
> a view update:
>
> update the
> (select t.c1 new_c1, t.c2 new_c2,
> e.c1 old_c1, e.c2 old_c2
> from table1 t, table2 e
> where e.pk_id = t.pk_id ) view1
> set view1.old_ c1 = view1.new_c1,
> view1.old_c2 = view1.new_c2;
>
> But I get an error because I cannot select multiple columns in the view. I
> wrote
> a stored procedure what has a cursor that walks through the smaller table and
> issues
> updates to the large one, and that works. Is there a better (faster) way to
> do this?

I would open the cursor linking both tables by the primary key and pulling the rowid from the larger table.

Then do your updates by rowid.

I know it sounds like it should take as much time ... but I have found in many cases that it is quite a bit faster.

Daniel A. Morgan Received on Thu Mar 01 2001 - 22:27:27 CST