Updating values based on 3 table join
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