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



  • 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


Log in to reply
 


Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2