Re: Update with SQL Question

From: Mark G <mgumbs_at_nospam.hotmail.com>
Date: Wed, 30 Jun 1999 09:09:12 +0100
Message-ID: <3779ce48.0_at_145.227.194.253>


You can try writing a pl/sql program with cursors to retrieve the values you want to update and then issue your update in a cursor loop. commiting after each update will speed things up as well.

M

Alan S wrote in message <3779f15f_at_news1.jps.net>...
>I need to update a value in one table based on the corresponding value in
>another table. In this case MPI_ID = OLD_EPI_NUM. NEW_EPI_NUM updated
into
>the MPI_ID field.
>
>
>UPDATE SX_TABLE
> SET MPI_ID = (SELECT NEW_EPI_NUM
> FROM MPI_XREF
> WHERE MPI_XREF.OLD_EPI_NUM = SX_TABLE.MPI_ID)
> WHERE EXISTS (SELECT NEW_EPI_NUM
> FROM MPI_XREF
> WHERE MPI_XREF.OLD_EPI_NUM = SX_TABLE.MPI_ID);
>
>
>Someone from this group has already helped me this far. This query does
>work, but it takes an very long amount of time. There are approximately
>25000 records and I let it run for over an hour before cancelling it. Is
>there any other approach that might speed thing up. We are running 7.3.4
on
>an HP K Series 9000. Insert queries and others take mere seconds to run.
>
>Thank you,
>
>Alan
>
>
>
>
Received on Wed Jun 30 1999 - 10:09:12 CEST

Original text of this message