CODEWITHSUNDEEP
pythonsqlsql-serversql-updatesql-insert
Daedalus Mythos
Daedalus Mythos

Reputation: 575

Update and select in one operation in MSSQL

I have a table with a column ID varchar(255) and a done bit. I want fetch the first ID found, where the bit isn't set and whilst fetching also set the bit. So that no other instance of the script uses the same ID and no race condition is possible.

import _mssql
con = _mssql.connect(server='server', user='user', password='password', database='default')

#these two in a single command
con.execute_query('SELECT TOP 1 ID FROM tableA WHERE done=0')
con.execute_query('UPDATE tableA SET done=1 WHERE ID=\''+id_from_above+'\'')
for row in con:
    #row['ID'] contains nothing as it last used with the UPDATE, not the SELECT
    start_function(row['ID'])

edit (including the suggestion of wewesthemenace):

[...]
con.execute_query('UPDATE tableA SET done = 1 WHERE ID = (SELECT TOP 1 ID FROM tableA WHERE done = 0)')
for row in con:
    #row['ID'] contains nothing as it last used with the UPDATE, not the SELECT
    start_function(row['ID'])

Working on Microsoft SQL Server Enterprise Edition v9.00.3042.00, i.e. SQL Server 2005 Service Pack 2

edit 2:
The answered question lead me to a follow-up question: While mssql query returns an affected ID use it in a while loop

Upvotes: 0

Views: 105

Answers (2)

Daedalus Mythos
Daedalus Mythos

Reputation: 575

Possible solution, which works in my situation.

con.execute_query('UPDATE tableA SET done=1 OUTPUT INSERTED.ID WHERE ID=(SELECT TOP(1) ID FROM tableA WHERE done=0)')
for row in con:
    #row['ID'] is exactly one ID where the done bit wasn't set, but now is.
    start_function(row['ID'])

Upvotes: 0

Felix Pamittan
Felix Pamittan

Reputation: 31879

How about this one?

UPDATE tableA SET done = 1 WHERE ID = (SELECT TOP 1 ID FROM tableA WHERE done = 0)

Upvotes: 1

Related Questions