I have three tables orders, customer_old and customer_new.

  • The orders table contains the old_user_login and associated order number.
  • The customer_old table contains the information on customer, namely old_user_login and user_fullname.
  • The customer_new table contains new_user_login and user_fullname.

The idea is to replace the old_user_login with new_user_login for all the rows in the orders table, and the only thing that matches is the user_fullname in the two customer tables. The old customer table will effectively be made redundant and deleted after this operation is done.

How do I go about doing this?

1 Answer 1


Here is an example of how that could be done:

--demo setup
Declare @Orders table (OrderNumber int, UserLogin varchar(20))
Declare @CustomerOld table (UserLogin varchar(20), UserFullName varchar(30))
Declare @CustomerNew table (UserLogin varchar(20), UserFullName varchar(30))

insert into @Orders (OrderNumber,UserLogin) values(1,'SillySam')
insert into @CustomerOld(UserLogin,UserFullName) values('SillySam','Sam Snyder')
insert into @CustomerNew(UserLogin,UserFullName) values('SillySamUpdated','Sam Snyder')

SET o.UserLogin = cn.UserLogin  --update orders.UserLogin with value retrieved from CustomerNew
FROM @Orders o
JOIN @CustomerOld co        --join CustomerOld to Orders using UserLogin
    ON co.UserLogin = o.UserLogin
JOIN @CustomerNew cn        --join CustomerNew to CustomerOld using UserFullName from both tables
    ON cn.UserFullName = co.UserFullName

select * from @Orders

OrderNumber UserLogin
1           SillySamUpdated
  • thank you, if I wanted to test this on a single user how would I construct the WHERE clause, and would I base this on the old login, new login, or the full name?
    – SimStil
    Sep 9, 2019 at 11:32
  • @SimStil - where co.UserLogin = 'OldLogin' or where o.UserLogin = '???' Sep 9, 2019 at 11:35
  • @SimStil - You can always run the update (and select) inside a transaction and verify before committing it. Sep 9, 2019 at 11:37
  • This worked perfectly, thank you!
    – SimStil
    Sep 9, 2019 at 11:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.