Re: Oracle's update statement

From: Jaap W. van Dijk <j.w.vandijk.removethis_at_hetnet.nl>
Date: Fri, 03 Nov 2006 22:46:36 GMT
Message-ID: <454bc1a9.2958171@news.hetnet.nl>

On 3 Nov 2006 08:52:26 -0800, tonixlaw_at_gmail.com wrote:

>Hi all,
>
>I am trying to partially update a table based on a heavy query.
>
>If I use following update:
>
>update
> table_A
>set
> (ColA, ColB) =
> (select ColA, ColB
> from heavy_query_B alias_B
> where id = table_A.id)
>where
> exists
> (select *
> from heavy_query_B alias_B
> where id = table_A.id)
>
>It would be extremely slow. The "where" clause is required to avoid
>updating unwanted rows in table_A, however i think it's one of the
>problems that impact performance.
>
>Any idea that could help?
>
>Thanks a lot!
>

If you are on 10g you can use the MERGE statement. Something like:

MERGE INTO table_A
USING (select ColA,ColB,id from heavy_query_B) alias_B ON ( table_A.id = alias_B.id)
WHEN MATCHED THEN SET colA = alias_B.ColA,ColB= Alias_B.ColB

This cannot be done in earlier versions because the INSERT part of the MERGE was not optional then.

If Oracle can determine, for instance by means of a primary key or unique key, that the id of heavy_query_B is unique, you could also use:

UPDATE
(SELECT

table_A.colA A_colA, 
table_A.colB A_colB,
alias_B.colA B_colA,
alias_B.colB B_colB

FROM
table_A,
(select colA,colB,id
from heavy_query_B) alias_B
where alias_B.id = table_A.id)
SET
A_colA = B_colA,
A_colb = B_colB

regards,

Jaap.

Regards,

Jaap. Received on Fri Nov 03 2006 - 16:46:36 CST