Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3930
  • Last Modified:

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.
0
fidsplice
Asked:
fidsplice
  • 3
  • 2
1 Solution
 
chaauCommented:
You need to modify the query a bit:
Update tblRequest 
set reference = (select mindate from mindates where mindates.rid = tblRequest.id )
where  exists (select 1 from mindates where mindates.rid = tblRequest.id)

Open in new window

0
 
dannygonzalez09Commented:
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 null

Open in new window

0
 
fidspliceAuthor Commented:
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.
0
A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

 
chaauCommented:
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
0
 
fidspliceAuthor Commented:
Thanks Chaau for the tip, The SQLite forum provided an answer and it was so simple. Where my brain lost touch with reality I don't know but the answer was simpler, not more complicated. After 45 years in this game why haven't I learned to stop and regroup!!  Oh well!

It is thus:
update tblRequest
   set reference = (select min(Created)
                      from tblEventLog
                     where RequestID = tblRequest.ID)
 where Created is null;

Boy!!!
0
 
fidspliceAuthor Commented:
Advice from a product specific forum, with (I suppose) a subject expert providing an elegant answer. I merely relayed his offering.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now