Hi folks,
I have a problem! The scripts below are are partly based on guidance I
found on the web, and as I started iterating to try to get a resolution.
They are not elegant! But here 'tis.
Using 3.8.3.1 - could not see any issues related to my problem in later
updates so haven't yet upgraded customer. Hate saying "I need to put in an
update" and it doesn't solve the problem. Embarrassing!
*My problem*
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:
*My attempts*
-- select only one statement at a time to execute
-- SQLite repair of omitted 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
Update
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.
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
