Don't get discouraged. This can be fixed.
Consider this.
SELECT U.CinemaCode, U.TransNumber
FROM tblDWConcession U
WHERE U.LoyaltyMemberCode IS NULL AND U.IsProcessCompleted IS NULL
AND SalesTransCinemaDate>(SELECT DateAdd(Year, -1, GetDate()) As OneYearAgo);
According to your earlier statement, that should return 4-5 rows correct? (Test it)
So lets use that 4-5 row query to drive our update.
UPDATE U
SET U.LoyaltyMemberCode = (SELECT TOP 1 P.LoyaltyMemberCode FROM tblDWPayment P WHERE P.CinemaCode = U.CinemaCode and P.TransNunmber = U.TransNumber),
U.LoyaltyClubID = (SELECT TOP 1 P.LoyaltyClubID FROM tblDWPayment P WHERE P.CinemaCode = U.CinemaCode and P.TransNunmber = U.TransNumber)
FROM tblDWConcession U
WHERE U.LoyaltyMemberCode IS NULL AND U.IsProcessCompleted IS NULL
AND SalesTransCinemaDate>(SELECT DateAdd(Year, -1, GetDate()) As OneYearAgo);
So the corelated subquery in the top 2 lines will be executed 4-5 times. It should be no problem at all but you wouldn't want to use it for a million rows.
Here is the thing. That statement I just gave is ALREADY equivalent to the one below, but potentially less efficient.
UPDATE U
SET U.LoyaltyMemberCode = P.LoyaltyMemberCode,
U.LoyaltyClubID = P.LoyaltyClubID
FROM tblDWConcession U
INNER JOIN tblDWPayment P ON P.CinemaCode = U.CinemaCode AND P.TransNumber = U.TransNumber
WHERE U.LoyaltyMemberCode IS NULL AND U.IsProcessCompleted IS NULL
AND SalesTransCinemaDate > (SELECT DateAdd(Year, -1, GetDate()) AS OneYearAgo);
And that statement is equivalent to the one in your CTE with the CTE removed. So why is it not working? Probably because it does not prioritize the date index. Instead it prioritizes PKs. I could say more about that, maybe later. Anyway, try the UPDATE using corelated subqueries keeping in mind it is just a trick to change the query plan.