Is the WITH (NOLOCK) hint ignored on the non-target tables of an UPDATE or DELETE statement?

Question

In SQL Server Databases, the WITH (NOLOCK) query hint is ignored on target tables in UPDATE and DELETE statements. However, does this also apply to tables included in a join for an update or delete? For instance, is:

update t
set value1 = 123
FROM mytable t
inner join othertable o with (nolock) on t.id = o.id

equivalent to:

update t
set value1 = 123
FROM mytable t
inner join othertable o on t.id = o.id

Answer 1

No it is not ignored. This is easy to test.

Setup

DROP TABLE IF EXISTS mytable, othertable

CREATE TABLE mytable(id INT, value1 INT)
CREATE TABLE othertable(id INT, dirtyvalue INT)

INSERT mytable VALUES (1, NULL)
INSERT othertable  VALUES (1, 100)

Connection 1

BEGIN TRAN

UPDATE othertable SET dirtyvalue = 999

WAITFOR DELAY '00:02:00' /*To give time to run connection 2*/

ROLLBACK

Connection 2 (whilst above still running)

update t
set value1 = o.dirtyvalue
output inserted.value1
FROM mytable t
inner join othertable o  with (nolock) on t.id = o.id

The uncommitted 999 value is used in the update rather than 100 as would have happened without the hint if running at RCSI or being blocked if running at RC locking isolation level.

Note the 999 value is never actually committed to othertable so the use of NOLOCK here means that the state persisted in mytable never logically existed.