Yikes, sorry about the subject... accidentally hit enter with focus on the submit button
My leads table has a lead_id, counselor_id and market_id
If a leads counselor_id is updated then the lead_id's market_id should also be updated to the market_id associated with the new counselor_id.
The market2users table is simple it has market_ids mapped to user_ids.
So if a leads counselor_id is currently set to 10005 and it is updated to 10004 then the leads market_id should update to the related counselor_id in the Market2users table.
I am trying to do this update, it does not throw an error and only update the l.counselor_id but not the l.market_id.
leads as l
INNER JOIN markets2users as m2u
ON m2u.user_id = l.counselor_id
l.counselor_id = 10004
,l.market_id = m2u.market_id
l.lead_id = 1
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? If so, can you suggest a way to do this?