SQLite Update a table from values in another table is not applying the correct source row value.
Posted on 2014-03-17
I'm trying to repair a table (tblrequest) which has a date "created". Some were left null, and I want to update those with the earliest activity date for that request from an event log.
The update is working but is applying the "mindate" of the first row in the work table to all candidate updates.
How do I apply the right value to the correct tblrequest?
Working script which I highlight and run one step at a time follows:
-- select only one statement at a time to execute
-- SQLite repair of ommitted created date from earliest event log.
-- CW 18/03/2014
drop table mindates
create table mindates
(mindate date, rid int)
insert into mindates
select Min(tblEventLog.Created) as mindat , tblrequest.id as rid
From tblrequest join tblEventLog where tblRequest.ID = tblEventLog.RequestID and tblrequest.created isnull
group by tblrequest.id
tblRequest set reference = (select mindate
from tblrequest inner join mindates on tblrequest.id = mindates.rid where tblrequest.created isnull )
where exists (select * from mindates where mindates.rid = tblrequest.id)
Appreciate any guidance.