Re: SQL Update from Select problem
Date: Thu, 19 Dec 2002 07:08:04 GMT
Message-ID: <ofeM9.394048$WL3.117197_at_rwcrnsc54>
What Oracle version, what is the explain plan, and what does tkprof say?
Jim
"Steve" <steve_ackermann_at_hotmail.com> wrote in message
news:42733b5.0212182244.57fa0561_at_posting.google.com...
> This SQL runs indefinitely and takes 95% CPU. However, if I create a
> temporary table (say R_VEHICLE_TEMP) and modify the SQL slightly to
> update that instead of R_VEHICLE, the SQL only takes 5 seconds to run
> correctly. Why can't I select and update from/to the same table?
>
> UPDATE R_VEHICLE rv1
> SET (RANK, VPCT) = ( select rank,vpct
> from (SELECT w_vhc_id,
> DENSE_RANK() OVER(ORDER BY VPSUM DESC) AS RANK,
> PERCENT_RANK() OVER(ORDER BY VPSUM ASC) AS VPCT
> FROM R_VEHICLE rv2
> WHERE VERSION_ID = 19
> and vpsum > 0) v1
> where rv1.w_vhc_id = v1.w_vhc_id)
> where VERSION_ID = 19
> and vpsum > 0
> /
>
> Thanks for any help,
> Steve
Received on Thu Dec 19 2002 - 08:08:04 CET