Re: Two records a year apart
Date: 15 Jan 2002 16:01:52 -0800
Message-ID: <da20daf0.0201151601.3eaf0ed3_at_posting.google.com>
Hi Philip
Try this
UPDATE mcd.sumps a
SET a.comp = 1
WHERE EXISTS ( SELECT b.mstore
FROM mcd.sumps b WHERE a.mstore = b.mstore AND a.mperiod - b.mperiod > 365)
sometimes, EXISTS clause is more efficient then the IN clause
hth
Chris
"Philip Morrow" <cracker_at_mymorrow.com> wrote in message news:<_ZY08.319763$oj3.65754398_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);
Received on Wed Jan 16 2002 - 01:01:52 CET