Update

Databases
1

Yikes, sorry about the subject… accidentally hit enter with focus on the submit button :frowning:

Hi,

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.


UPDATE
    leads as l
INNER JOIN markets2users as m2u
    ON m2u.user_id = l.counselor_id 
SET
    l.counselor_id = 10004
    ,l.market_id = m2u.market_id
WHERE
    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?

Thanks.

2

Well this is kind of ugly but it does the trick. It look like the answer to this quesiton is yes.

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?

Here is what worked:


SELECT * FROM `users`.`leads`;
UPDATE
	leads as l
INNER JOIN markets2users as m2u
	ON m2u.user_id = l.counselor_id
SET
	l.counselor_id = 10004
	,l.market_id = (SELECT market_id FROM markets2users where user_id = 10004)
WHERE
	l.lead_id = 1