LIMIT 1 in update query updates all rows instead of just one postgres

Question

I have this query which is only supposed to update a row and return the row that is updated but it updates all the rows instead of the limit 1 that is returned from the select. I am trying to achieve is explicit locking on a row so that no concurrent reads/selects can occur when any of the rows are selected with stats = pending (sort of like a queue)

This is the query:

BEGIN;
UPDATE tasksq
SET    stats = 'active' 
WHERE  stats = (
         SELECT stats
         FROM   tasksq
         WHERE  stats = 'pending'
         ORDER BY jobid 
         LIMIT 1
         FOR UPDATE SKIP LOCKED
         )
RETURNING *;
COMMIT;

If i run only the limit 1 query then it does work and returns 1 row

SELECT *
FROM   tasksq
WHERE  stats = 'pending'
ORDER BY jobid 
LIMIT 1
FOR UPDATE SKIP LOCKED

Find snippet of data set below:

Answer 1

This subquery

     SELECT stats
     FROM   tasksq
     WHERE  stats = 'pending'
     ...

will of course return 'pending'. This makes the statement

UPDATE tasksq
SET    stats = 'active' 
WHERE  stats = 'pending'

So, you update all rows with stats = 'pending' to stats = 'active'.

You probably want

UPDATE tasksq
SET    stats = 'active' 
WHERE  jobid = (
         SELECT jobid
         FROM   tasksq
         WHERE  stats = 'pending'
         ORDER BY jobid 
         LIMIT 1
         FOR UPDATE SKIP LOCKED
         )

With jobid being the primary key, this updates one row (the one found in the subquery).