I'm trying to update some records on Table C (contacts.assigned_used), but I have a Join from Table A (leads) and passing through in intermediary Table C.
The query to show the filtered Table C (which is what I want to update) is:
SELECT
contacts.*
FROM
leads
INNER JOIN leads_contacts_c ON leads.id = leads_contactsleads_ida
INNER JOIN contacts ON leads_contacts_c.leads_contactscontacts_idb = contacts.id
WHERE
leads.assigned_user_id = '7077c616-4f2c-f966-d9b6-5581807bae86';
So this query basically will show all the contacts related to the Lead assigned to certain user. On my next step once I get the list of all the contacts, is that I would like to assign all the contacts to the same Lead "owner" because some of the contacts are assigned to other employees that no longer work on our company and the new employee cannot see the record.\
After some trial and error, and not the most intuitive way of doing it the code that worked form me was the following:
UPDATE
leads h
INNER JOIN leads_contacts_c i ON h.id = i.leads_contactsleads_ida
INNER JOIN contacts j ON i.leads_contactscontacts_idb = j.id
SET j.assigned_user_id = '7077c616-4f2c-f966-d9b6-5581807bae86'
WHERE
h.assigned_user_id = '7077c616-4f2c-f966-d9b6-5581807bae86';
Note that I'm using "temporary" tables, because of the way MySQL works, if you do it the intuitive way the UPDATE cannot have the same table on the UPDATE and the WHERE Clause so you have to create "temporary" tables to have with accomplished.