Can you do an update of a column while selecting for the same column

Question

In MS SQL I am doing this - I think it's working correctly but want to check.

UPDATE BillingInvoice 
SET CustomerId = Customer.CustomerId 
FROM Customer 
WHERE BillingInvoice.CustomerId = Customer.CustomerNum 
      AND BillingInvoice.CreatedBy = 222

This is a migration from an old database, what we are doing is first dumping the old customer number into CustomerId, then joining the Customer table, and getting the PK of the customer table, then finally putting back that PK into CustomerId. It does seem to work but want to check that SQL is processing this correctly.

Answer 1

You can use the OUTPUT clause to see which changes have been made :

UPDATE BillingInvoice 
SET    CustomerId = Customer.CustomerId 
OUTPUT inserted.CustomerId, inserted.CreatedBy, inserted.CustomerId, deleted.CustomerId_OLD
FROM   Customer 
WHERE  BillingInvoice.CustomerId = Customer.CustomerNum 
       AND BillingInvoice.CreatedBy = 222

Alternatively you can put the results of the OUTPUT clause into a table (temporary or table variable) to query it

Answer 2

I use this technique:

-- BEGIN the transaciotn here
BEGIN TRAN

-- UPDATE here
UPDATE [AdventureWorks2019].[Person].[PersonPhone]
SET PhoneNumber = '222'
WHERE BusinessEntityID BETWEEN 1 AND 5

-- SELECT the change
SELECT TOP 10 * FROM [AdventureWorks2019].[Person].[PersonPhone]

-- ROLLBACK everything
ROLLBACK TRAN 

-- SELECT if everything was rolled back 
SELECT TOP 10 * FROM [AdventureWorks2019].[Person].[PersonPhone]

Here is the outcome on AdventureWorks2019: