Brayn Brayn - 1 month ago 22
SQL Question

LIMIT equivalent for SQL Server 2012 in an UPDATE statement

I have the following table below and am trying to update the first available row with an user ID through a query, but I need to limit this to only update one row and not multiple.

ID Model UserID
1 X12T5 1
2 X13T5 2
3 X14T5 NULL
4 X15T5 NULL


The first available row would be where ID is 3. I would update it with the following query:

UPDATE Table SET UserID = '3' WHERE UserID IS NULL


But I want to make sure it affects only 1 row and not multiple that are available, LIMIT doesn't exist in SQL Server.

What would the best way to achieve this?

Answer

You can do this with UPDATE TOP. It's the equivalent of a SELECT TOP but for updates; and TOP is SQL Server's equivalent of MySQL's LIMIT.

See further info.