Well this is kind of ugly but it does the trick. It look like the answer to this quesiton is yes.
Here is what worked:
So even though I am joining the market2users table to the leads counselor_id is it not finding the correct market2user mapping because it matches the current counselor_id rather than the l.counselor_id that will exist after the update?
SELECT * FROM `users`.`leads`;
leads as l
INNER JOIN markets2users as m2u
ON m2u.user_id = l.counselor_id
l.counselor_id = 10004
,l.market_id = (SELECT market_id FROM markets2users where user_id = 10004)
l.lead_id = 1