Link to home
Start Free TrialLog in
Avatar of thready
thready

asked on

update with select followed by where clause

Hi Experts,

If I have a query like so:
UPDATE Tasks
SET Status='RESERVED', MachineName='me'
FROM Tasks t 
INNER JOIN (SELECT TOP 100 * FROM Tasks WHERE Status='QUEUED' AND MachineName='') t2 
ON t.ID = t2.ID
WHERE t.MachineName=''

Open in new window


Basically, I have multiple machines running this query, competing to grab tasks and reserve them for themselves (and the 'me' string changes according to the name of the machine doing the query).  The machines are trying to reserve tasks - and once a row gets reserved, I cannot allow any other machine to be allowed to modify that row.

I'm wondering if the last WHERE clause (WHERE t.MachineName='') is evaluated after the inner join is complete.  In other words - I am only allowing the row to update as long as the MachineName column is empty.

Technically, if this query run simultaneously as the same query on another machine, the 2 updates can complete

1- Does this do what I want and the last WHERE is evaluated just before that row is worked on, in which case that row is "protected" from double modification.. or
2- the last WHERE is included in the join somehow and the row can be modified by 2 machines?

Thanks for your help!
Mike
ASKER CERTIFIED SOLUTION
Avatar of Jim Horn
Jim Horn
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of thready
thready

ASKER

I do trust you, but is there a way I can prove this to my superiors?  This is a concern...
Avatar of thready

ASKER

I have two colleagues that say otherwise.  I'm still with you however.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of thready

ASKER

Thanks