SQL select given another query

Question

So I would like to search for the max time in a column:

sqlite> SELECT max(strftime('%Y-%m-%d %H:%M',time)) FROM posts WHERE thread_id=123456;
2012-10-02 02:31

For each thread_id returned by this query:

sqlite> SELECT thread_id FROM threads WHERE out_of_date=0;
111
123
187
...

Then I would like to search for all threads whose last_post field don't match the time field returned by the first query, and set the out_of_date field to 1:

sqlite> UPDATE threads SET out_of_date=1 WHERE thread_id=123456 AND last_post!='2012-10-02 02:31';

Problem is, I'm not too sure how I should go about combining these three separate queries. Any thoughts?

acatt · Accepted Answer · 2012-10-04 22:04:50Z

1

The below SQL should update the threads table correctly. It uses a correlated sub-query to combine the 1st and 3rd of your queries. This can then be combined with your 2nd query by adding it's WHERE clause.

UPDATE threads T
SET out_of_date = 1
WHERE out_of_date = 0
    AND last_post != (
        SELECT MAX(strftime('%Y-%m-%d %H:%M',time))
        FROM posts
        WHERE thread_id = T.thread_id
    )
;

edited Oct 4, 2012 at 22:04

answered Oct 4, 2012 at 21:55

acatt

4873 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

wrongusername Over a year ago

Excellent! Many thanks! By the way, did you mean to put strftime by itself? It seems like that would be asking to select the maximum of the strftime column, which does not exist.

acatt Over a year ago

@wrongusername I tested it with a column I named strftime but forgot to change it in the answer! Thanks for pointing that out.

Collectives™ on Stack Overflow

SQL select given another query

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related