Updating column with values from the same table

[Jens Riedel]

Hi,

I'm looking for an update statement that allows me the following operation:

I have a table where I have records for each day with the fields
'day_date', 'ID', 'my_value'.
When a new record comes in for the current day I want to update the
field 'my_value' with the corresponding value from the previous day's
record with the same ID - if it exists, sometimes there are new records
that have no equivalent already.

An example:

Record for today: 2013-01-17 ; 4711 ; null

-> set the third field to the value that is in 'my_value' from the
record ' 2013-01-16 ; 4711; xyz ' or leave it null if this record
doesn't exist.

I appreciate any idea how to achieve this.

Thanks and regards
Jens

[Jens Riedel]

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);

[joel garry]

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
--
@home.com is bogus.
http://www.pcadvisor.co.uk/news/small-business/3420966/oracle-trying-quell-certification-exam-voucher-fraud/

[Dan Blum]

joel garry <joel-...@home.com> wrote:

> On Jan 17, 12:02?am, Jens Riedel <jens...@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 to...@panix.com
"I wouldn't have believed it myself if I hadn't just made it up."