Re: Updating column with values from the same table

From: joel garry <joel-garry_at_home.com>
Date: Thu, 17 Jan 2013 08:57:02 -0800 (PST)
Message-ID: <f03c86d5-8fe8-4404-a7b7-18394be3f39a_at_a8g2000vby.googlegroups.com>

On Jan 17, 12:02 am, Jens Riedel <jens..._at_gmx.de> wrote:
> O.k., I already found a solution that seems to work fine (see below).
> I tested it with a dozen records, but the production table will have
> several hundred thousand records for each day - so if anyone knows a
> different way to to this that should have a better performance any hint
> is appreciated!
>
> Regards
> Jens
>
> update mytable t1
> set t1.KZ1 = (select KZ1
>                from mytable t2
>                where to_char(t2.DAY_DATE, 'YYYY-MM-DD') = '2013-01-16'
>                  and t2.ID = t1.ID)
> where to_char(t1.DAY_DATE, 'YYYY-MM-DD') = '2013-01-17'
>    and EXISTS (select KZ1
>                from mytable t2
>                where to_char(t2.DAY_DATE, 'YYYY-MM-DD') = '2013-01-16'
>                  and t2.ID = t1.ID);

You might try a function based index on the ID and truncated date, and you might compare it to trunc(sysdate) and trunc(sysdate)-1. (Totally untested and off the top of my head.)

jg

--
_at_home.com is bogus.
http://www.pcadvisor.co.uk/news/small-business/3420966/oracle-trying-quell-certification-exam-voucher-fraud/

Received on Thu Jan 17 2013 - 17:57:02 CET