Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Can I Select and Update at the same time?

Tags:

sql-server

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?

like image 973
Ed Manet Avatar asked Feb 19 '10 13:02

Ed Manet


People also ask

Can we use select and UPDATE together?

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.

Can you use the UPDATE and select clauses in one SQL 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.

What is the use of select for UPDATE?

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.


1 Answers

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
like image 102
Lieven Keersmaekers Avatar answered Oct 16 '22 20:10

Lieven Keersmaekers