1

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

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
  • 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 at 11:32
  • @SimStil - where co.UserLogin = 'OldLogin' or where o.UserLogin = '???' – Scott Hodgin Sep 9 at 11:35
  • @SimStil - You can always run the update (and select) inside a transaction and verify before committing it. – Scott Hodgin Sep 9 at 11:37
  • This worked perfectly, thank you! – SimStil Sep 9 at 11:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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