I have a query like this:
UPDATE subscribers AS s SET status = 1
FROM (SELECT id FROM subscribers
WHERE client_id = *** AND status = 0 ORDER BY id LIMIT 5) AS t
WHERE t.id = s.id AND status = 0 RETURNING *
(explanation: SELECT all rows that have status=0 BUT update them to status=1 and only bring me FIVE as maximum).
I HAVE 11 rows in subscribers table with status = 0
The issue I'm having is: when I execute this query in my code (node.js, it doesn't matter what programming language, it's a query problem) in the FIRST TOW loops (first queries) it's returning 5 ROWS (that's good, as i have LIMIT 5 in the inner query).
But in the LAST QUERY I have 0 records... and in my table there's a row with status = 0 WAITING THERE TO BE FETCHED
Somebody has a clue? I'm stuck with this
I have to say that my code was sending queries in parallel (almost at the same time), and it was creating a "race" condition, because the inner query of "subsequents" queries was receiving OLD data (before the outer UPDATE of the first query)... so this was a RACE CONDITION... i solved it after a while, thanks for your help, i'm learning a lot from this.