Re: Updating column with values from the same table

From: Jens Riedel <jensrie_at_gmx.de>
Date: Thu, 17 Jan 2013 09:02:36 +0100
Message-ID: <alppgsFiq6qU1_at_mid.individual.net>

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

Received on Thu Jan 17 2013 - 09:02:36 CET