Can you do an update of a column while selecting for the same column
Bogopo last edited by
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.
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