UPDATE using locking hint

[mkal]

I've inherited an application that calls a number of stored procedures. In quite a few of them I've been finding updates that look like the following:

UPDATE tbl_name
SET field_name = 'some value'
FROM tbl_name WITH (NOLOCK)
WHERE field_name = 'some other value'

I didn't think you could use the "WITH (NOLOCK)" hint when UPDATING,DELETTING or INSERTING.

Should the UPDATE be re-written to:

UPDATE tbl_name
SET field_name = 'some value'
WHERE field_name = 'some other value'

or can I just ignore it?

 

[markros]

Looks like this hint just ignored - no error is generated.

However, it may make sense to re-write without it, since BOL states

WITH ( <Table_Hint_Limited> )
Specifies one or more table hints that are allowed for a target table. The WITH keyword and the parentheses are required. NOLOCK and READUNCOMMITTED are not allowed. For information about table hints, see Table Hints (Transact-SQL).

for the UPDATE command.

 

[mkal]

Figured as much, nice to have some confirmation. I'll probably pull it out.

Thanks