Re: Update question

<tombest_at_firstusa.com> wrote in message news:97m28k$dm4$1_at_news.netmar.com...
> 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.

You could try to do it directly:

   update table2 e set (c1,c2) = (select c1,c2 from table1 t where t.pk_id = e.pk_id);

To make sure table2 rows, that don't have corresponding pk_id in table1 are not
updated (c1,c2 not set to null), add an extra check:

   update table2 e set (c1,c2) = (select c1,c2 from table1 t where t.pk_id = e.pk_id)

   where exists (select 1 from table1 where table1.pk_id = e.pk_id);

If table2 is realy huge, you may consider updating it in chunks, by adding

   and e.pk_id between N1 and N2
(where N1 and N2 are between min(pk_id) and max(pk_id) from table), and running
the statement several times.

Best regards, Alexei
.. Every program is a part of some other program, and rarely fits Received on Fri Mar 02 2001 - 00:53:20 CST