is there an SQL Server equivalent of PostgreSQL “select * for update” without opening cursor

Postgresql allows me to lock the row while selecting it with the following syntax:

select id, amount from table where id = 1234 for update

This allows me to update the row while ensuring that concurrent reads who also want to update the same row will get updated and correct results.

  • Mapping the map datatype on grails with SQL Server
  • Way to restrict the number of times a value can appear in a table
  • How to make a temporary variable act as * in a select statement, in t-sql
  • Delphi7: Get SQL server system Date and Time format
  • IF UPDATE() in SQL server trigger
  • Insert rows into a temp table having one and only one column
  • Basically I can do something like this (pseudocode):

    begin transaction
    select id, amount from table where id = 1234 for update
    if(amount == new_amount) then
      delete from table where id = 1234;
    else
      update table set amount = amount - new_amount where id = 1234
    end
    commit transaction
    

    The same syntax is allowed in Sql Server only for DECLARE CURSOR. Is there a way to read the row while locking it for update with just simple select?

    One Solution collect form web for “is there an SQL Server equivalent of PostgreSQL “select * for update” without opening cursor”

    select * from T WITH (UPDLOCK)
    

    The WITH hints are very flexible and well designed. I guess they are more precise and general than for update.

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.