Did you try saying your request directly?
update tblRequest
set reference = (select min(Created)
from tblEventLog
where RequestID = tblRequest.ID)
where Created is null;
* note that this will set null any references already null for which the result
of the correlated subquery is null. There are ways to avoid this but they are
probably overly expensive. Of course, you have an index on tblEventLog that
has ID as the first column (and Created in it as well, for maximum performance).
* are you sure you want to update "reference" and not "Created"? If so, do you
not think you might also want to only update rows where Created is null or
Reference is null?
>-----Original Message-----
>From: [email protected] [mailto:sqlite-users-
>[email protected]] On Behalf Of Chris Waters
>Sent: Wednesday, 19 March, 2014 19:28
>To: [email protected]
>Subject: [sqlite] Problem with Update from associated table.
>
>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
>[email protected]
>http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
