Re: Updating column with values from the same table

From: Dan Blum <tool_at_panix.com>
Date: Mon, 21 Jan 2013 19:56:38 +0000 (UTC)
Message-ID: <kdk6hm$9ja$1_at_reader1.panix.com>

joel garry <joel-garry_at_home.com> wrote:
> 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.)

I would be hesitant to rely on sysdate, just in case the update process runs after midnight (even if it is not supposed to, things happen).

I would:

1. Add a flag which says whether we have tried to update the record yet.
2. Create the index Joel suggests.
3. Write the SQL like so:

update mytable t1

   set update_flag = 1,

       kz1 = (select t2.kz1
                from mytable t2
               where t2.id = t1.id
                 and trunc(t2.day_date) = trunc(t1.day_date)-1)

 where t1.update_flag = 0;

You don't need the EXISTS condition, since you want to set the value to NULL if there is no matching record. More importantly, you do not want to convert the dates to strings.

-- 
_______________________________________________________________________
Dan Blum					         tool_at_panix.com	
"I wouldn't have believed it myself if I hadn't just made it up."

Received on Mon Jan 21 2013 - 20:56:38 CET