Updating values based on 3 table join

Question

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?

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')

--solution
UPDATE o
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

--verify
select * from @Orders

---

OrderNumber UserLogin
1           SillySamUpdated