SQL — Update-select in ONE query

Select a batch of records while simultaneously updating them

Mike Huls

— 3 min read
SQL — Update-select in ONE query
How to keep track of which of these records you’ve selected? (image by Kailash Kumar on pexels)

This article will show you how to both update and select records simultaneously within a single query. This technique is especially practical for processing batches of records that have a state since it ensures you only select every record once. Among the advantages of the update-select are:

  • Atomic: this statement either executes both the select and the update succeed or nothing. No change of updating records that are not processed or selecting records that have the wrong status.
  • Performance: your database only has to perform one operation in stead of multiple separate ones
  • Coolness: impress others with this awesome query

We’ll demonstrate the power of this query through a practical example. First we’ll set up some tables and fill them with data.

0. Goal

You run a business that allows your users to write comics and submit them to a publication(magazines or newspapers e.g.). Before a comic gets published, the publication have to approve of the comic. Since they are very busy people it can take some time for a comic to be approved.

We’ve decided to keep track of the progress of a comic in a table called ComicStatus. This table contains the ComicName, the Publication and the State of the comic. The State reflects the comic’s state in the process of processing, submitting and acceptance or rejection by the publication. Let’s set up our table.

Every few minutes we run a process that does the following:

  • Take all new comics and submit them
  • Take submitted comics and check if the publication has processed them yet
  • If a comic is processed: update status to either accepted or rejected

Now let’s see how the select-update can help us with improving our process.

2. Selecting records in batches while updating status

We’ve decided to limit the process to a maximum of 5 comics at a time for development purposes. We’ll start with the most obvious way of handling this problem and then slowly improve it.

We’re herding all or records in one batch (image by Steven Lasry on Unsplash)

1 . The dumb way

Imagine we run our process every minute in the following way:

  1. Select top 5 records new records (with status 0)
  2. Loop through all of the records and submit each one to their publication
  3. wait for the publication API’s to approve of the comic
  4. update the comic’s status to either accepted or rejected

Since our process runs every minute, and approving the comic can take more than a minute, there is a change we select the same record twice. This can easily be fixed by quickly updating the status.

2. The better way

Before submitting the records we update the status of the selected records

  1. Select top 5 records new records (with status 0)
  2. Update top 5 of the selected records to status 1 (in process)
  3. Loop through all of the records and submit each one to their publication
  4. wait for the publication API’s to return a result
  5. update the comic’s status to either accepted or rejected

This way can still give an error. What if new records get inserted in our table between step 1 and 2? If this is the case we update unprocessed records and miss updating processed records.

3. The update-select way

We select the records we want and in the same query update their status.

  1. Update the top 5 records new records (with status 0) AND select them
  2. Loop through all of the records and submit each one to their publication
  3. wait for the publication API’s to return a result
  4. update the comic’s status to either accepted or rejected

This way ensures we don’t process records more than once. It doesn’t matter how long our process runs and when new records get inserted. How to perform step 1? Easier than you think:

That’s it! Pretty short right? We first update the top 2 records where the status is 0 and then output them. This output works like a regular select. Try to execute this query multiple times in a row; you’ll notice that you will never select the same record twice since it updates the status first. It will batch through our records until there are no more records with status 0.

Conclusion

Using this Update-Select ensures that we only update the records we’ve selected for processing and guarantees that either both succeed or the whole operation fails. It is a great new tool in your toolbelt just like these:

Happy coding!

— Mike

P.S: like what I’m doing? Follow me!

Read more