Is it safe to UPDATE a table field with WHERE clause checking that same field?

Question

I have something like this in mind:

UPDATE [MyTable]
SET [SomeField] = 1
WHERE [SomeField] is NULL

Will it work or is it like modifying a collection while iterating over it?

Or maybe write something like this?

UPDATE [MyTable]
SET [SomeField] = 1
WHERE [ID] IN (SELECT [ID]
               FROM [MyTable]
               WHERE [SomeField] is NULL)

Answer 1

Yes. The update operation will only occur on table rows that match the WHERE clause.

Think about it as the WHERE clause is selecting the rows followed by the SET clause updating those selected rows.

Answer 2

Just to be clear you only need to do

UPDATE [MyTable]
SET [SomeField] = 1
WHERE [SomeField] is NULL

as the where clause is only used to reduce the scope the update is applied to.

It sounds like you're coming into SQL from a development background so one piece of advise you might find useful is to try out your operations within a transaction. That way you can undo any mistakes you make and refine the sql you're working on.

To utilise this run

BEGIN TRAN

before you execute the command you want to test and then

ROLLBACK TRAN

to undo it, or

COMMIT TRAN

to make it permanent