SQLite Update a table from values in another table is not applying the correct source row value.
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
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.
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
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.
try this
Update t
set t.reference = m.mindate
from tblRequest t
Inner join mindates m
on t.id = m.rid
where t.created is nullASKER
Thanks guys, but still no joy. Remember this is SQLite.
Chaau, that works but gives me the same result. The inner select produces a data set, the where exists produces a true, but the update takes the first row from the produced data set.
dannygonzalez09 - the bind is that SQLite doesn't support a join in an "update".
Fortunately I've only got about 50 entries to correct so worst case is I'll do it by hand, but the apparent lack of a workable solution alarms me a bit! 'd love to find a solution.
Chaau, that works but gives me the same result. The inner select produces a data set, the where exists produces a true, but the update takes the first row from the produced data set.
dannygonzalez09 - the bind is that SQLite doesn't support a join in an "update".
Fortunately I've only got about 50 entries to correct so worst case is I'll do it by hand, but the apparent lack of a workable solution alarms me a bit! 'd love to find a solution.
It is strange, as it should work. What version of SQLite are you using? There was an update that was something to do with the UPDATE problem. I recommend you ask SQLite team by sending your question to this mailing list: sqlite-users@sqlite.org. D Richard Hipp himself monitors this list and replies the questions directly. I have asked there a few times. People there are very helpful
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Advice from a product specific forum, with (I suppose) a subject expert providing an elegant answer. I merely relayed his offering.
Open in new window