I want to write a query to find the LOWEST SEQUENCE for status = REQUEST_PENDING then update it after a certain condition.

For example:

If the person named A is done, it's status would change from "request_progressed" to "request_done"; then the next person B will have his status changed to "request_progressed" while the person C stays the same because its sequence is the 3rd.

So I just need to know how do I change the person B status by finding the lowest sequence which has "request_pending" status and THERE IS (SUPPOSED TO BE) ONLY ONE "request_progressed" on the table that is why when it is updated to "request_done" the next "request_pending" need to be updated to "request_progressed".

I tried the following:

update table
set status = 'request_progressed'
where status = 'pending' AND sequence = min(sequence)

CodePudding user response：

In MySQL - you can specify order by and limit in update queries:

update t
set status = 'request_progressed'
where status = 'request_pending'
order by sequence
limit 1

In PostgreSQL - you still need order by and limit but they can only be used inside a subquery:

update t
set status = 'request_progressed'
where (status, sequence) in (
    select status, sequence
    from t
    where status = 'request_pending'
    order by sequence
    offset 0 rows fetch first 1 row only
)