SQL — Update-select in ONE query

Select a batch of records while simultaneously updating them

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…