Menu
This is an over-simplified explanation of what I'm working on.
I have a table with status column. Multiple instances of the application will pull the contents of the first row with a status of NEW, update the status to WORKING and then go to work on the contents.
It's easy enough to do this with two database calls; first the SELECT then the UPDATE. But I want to do it all in one call so that another instance of the application doesn't pull the same row. Sort of like a SELECT_AND_UPDATE thing.
Is a stored procedure the best way to go?
973
The subquery defines an internal query that can be used inside a SELECT, INSERT, UPDATE and DELETE statement. It is a straightforward method to update the existing table data from other tables. The above query uses a SELECT statement in the SET clause of the UPDATE statement.
You can't. There's no convention in a SQL UPDATE statement for returning data. And vice versa — a SELECT statement doesn't write information to a table.
SELECT FOR UPDATE is a SQL command that's useful in the context of transactional workloads. It allows you to “lock” the rows returned by a SELECT query until the entire transaction that query is part of has been committed.
You could use the OUTPUT statement.
DECLARE @Table TABLE (ID INTEGER, Status VARCHAR(32))
INSERT INTO @Table VALUES (1, 'New')
INSERT INTO @Table VALUES (2, 'New')
INSERT INTO @Table VALUES (3, 'Working')
UPDATE @Table
SET Status = 'Working'
OUTPUT Inserted.*
FROM @Table t1
INNER JOIN (
SELECT TOP 1 ID
FROM @Table
WHERE Status = 'New'
) t2 ON t2.ID = t1.ID
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
