sql query to select and update

Question

I saw the below query in algoexpert where they say they can update the job id based on the previous query. I have seen an update query with a select clause inside it or the merge clause that inserts or updates depending on a match

Is there some SQL syntax that lets you do the below. Please can you point me to links to read up on this?

BEGIN TRANSACTION;
  SELECT * FROM jobs_table WHERE status = 'QUEUED' ORDER BY created_at ASC LIMIT 1;
  // if there's none, we ROLLBACK;
  UPDATE jobs_table SET status = 'RUNNING' WHERE id = id from previous query;
  COMMIT;

You could use UPDATE jobs_table SET ... WHERE EXISTS(...) instead. — wildplasser
– wildplasser, Commented Mar 25, 2022 at 12:09

Luuk · Accepted Answer · 2022-03-25 12:10:48Z

1

UPDATE jobs_table 
SET status = 'RUNNING' 
WHERE id IN (SELECT id 
             FROM jobs_table 
             WHERE status = 'QUEUED' 
             ORDER BY created_at ASC 
             LIMIT 1);

When no id is found in the sub-query, nothing is being updated.

answered Mar 25, 2022 at 12:10

Luuk

15.4k5 gold badges28 silver badges44 bronze badges

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

Collectives™ on Stack Overflow

sql query to select and update

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related