SQL Query – select and update at the same time

Whilst creating an application, which works from a central job list, I found myself wanting to perform both a select and update query at the same time. Essentially, marking jobs as collected, at the same time they are collected. This then allows me to run multiple instances of the same app, all picking up their own tasks.

How to update rows in SQL when running a select query

So, how do you update the same row’s that you’re collecting? Well, for my purpose, it was quite straight forward. I just create a date variable, populate it with the current date and time, update the rows with one query, and then use that again with a select. Here’s an example of how I did it:

DECLARE @date AS DATETIME 
SELECT GETUTCDATE()

UPDATE 
	[job_list] SET [collection_date] = @date 
WHERE 
	id IN 
	(
		SELECT 
			TOP (10) id 
		FROM 
			[job_list] 
		WHERE 
			([collection_date] IS NULL OR DATEADD(DAY,1,[collection_date]) < GETDATE()) 
			AND [processed_date] IS NULL 
		ORDER BY 
			id
	);

SELECT 
	id 
FROM 
	[job_list] 
WHERE 
	[collection_date] = @date 
ORDER BY 
	id

GetUTCDate() will return a string like this, 2021-05-11 20:11:07.127, which as you can see is down to the millisecond, which is fine for me, but you could also just as easily replace the date field with a client name, or even better, run a combination of them both. GUID’s are also a good option.

You’ll also notice that I used TOP (10) in my sub query, within the where statement of the update query. This is just to limit ho many jobs are collected at a time, the other options in the where statement should be self explaniatory.

I hope this help you.