Re: Two records a year apart

[Quoted] Hi Philip,

IMO if you use EXISTS instead of in you'll get a correlated subquery and not [Quoted] two fulle table scans.

update mcd.sumps a
set a.comp = 1
where EXISTS (

      select b.mstore
      from mcd.sumps b
      where (to_date(a.mperiod) - to_date(b.mperiod)) = 365);

Besides, maybe you'd better use MONTHS_BETWEEN = 12 to prevent errors due to [Quoted] leap years

  ... where MONTHS_BETWEEN (a.mperiod, b.mperiod) = 12)

Regards,

--
H. Hultink
Software Engineer
Stoas, Division IOAL
Wageningen, The Netherlands
"Activating Knowledge"
http://www.stoas.nl

e-mail: hhu_at_stopspam.stoas.nl
"Philip Morrow" <cracker_at_mymorrow.com> wrote in message
news:FYY08.319756$oj3.65752308_at_typhoon.tampabay.rr.com...


> I've got a table similar to this


>

> mperiod    date

> mstore    number

> tc    number

> tq    number

> tp    number

> comp    number

>

> I'm using this SQL to determine if, for any one record of say store 154 if

> there is a corisponding record for that store one year prior.  It seems to

> be taking a long time... any suggestions on a different way to do this

would


> help, or suggestions on a better way to write this SQL would help too.


>

> The explain plan lists two full table accesses.

>

> update mcd.sumps a set a.comp = 1 where a.mstore in (

>     select b.mstore from mcd.sumps b where (to_date(a.mperiod) -

> to_date(b.mperiod)) = 365);

>

>

>

>