I have this statement that tries to update a date field based on a lookup table where I have two date fields. In the lookup I have pairs of date, adjusted date. All I am trying to do is update my adjusted date field in my main table based on this lookup table (if I don't find MYDATE in the lookup table, the MAINTBL.ADJ_DATE should be equal to MYDATE). The query I am using is this:
update MAINTBL set ADJ_DATE = t1.DATE_ADJUSTED
when DATELKUP.DATE_ADJUSTED IS NULL
end as DATE_ADJUSTED
left outer join DATELKUP
on MAINTBL.MYDATE = DATELKUP.MYDATE
where MAINTBL.MYDATE = t1.MYDATE
I've created an index (nonunique) on MYDATE.
If I add to the where clause a single date, the update runs within 2 seconds. If I leave the statement as it is it ran for 25 minutes and still didn't finish.
What else can I try?