Update question

Update question

Post by tomb.. » Sat, 03 Mar 2001 02:52:20



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?

Any help is appreciated.

 -----  Posted via NewsOne.Net: Free (anonymous) Usenet News via the Web  -----
  http://newsone.net/ -- Free reading and anonymous posting to 60,000+ groups
   NewsOne.Net prohibits users from posting spam.  If this or other posts

 
 
 

Update question

Post by Daniel A. Morga » Sat, 03 Mar 2001 13:27:27


Quote:> 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

 
 
 

Update question

Post by Alexei Olkhovski » Sat, 03 Mar 2001 15:53:20



> 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

 
 
 

1. Cached Updates QUESTION: Sorting table of updated and non-updated records

When using cached updates I am experiencing a strange phenomena:

The table is constantly sorted when trying to insert new records:
The new, not yet updated records come before the old, updated records..

Why why, oh why???

Please mail me personally and I will post an answer to the newsgroup.

Thanks in advance,
Christian Loft

2. Memory Tuning

3. Simple XML Update question - Update a tag.

4. MSFlexGrid

5. Update question and SQL question

6. CT, MA, NY - PICK Programmers

7. SQL Join data and Update Question

8. MSDE 2000 SP2 Problem

9. Simple RDO add/update question

10. SQL Update questions

11. batch updates question

12. UPDATE Question

13. DTS source table Update Question