mysql: update or delete with join operators (contacts)

Same as previous post but this time with Accounts instead of Leads.

List the Contacts related to the Accounts this employee has assigned:

SELECT
contacts.*

FROM
accounts
INNER JOIN accounts_contacts ON accounts.id = accounts_contacts.account_id
INNER JOIN contacts ON accounts_contacts.contact_id = contacts.id

WHERE

accounts.assigned_user_id = '7077c616-4f2c-f966-d9b6-5581807bae86'
AND contacts.deleted = 1
;

*** Now that I have done this, I might as well DELETE from the Database the Deleted Contacts, using the following query:

DELETE
x
FROM
accounts x
INNER JOIN accounts_contacts y ON x.id = y.account_id
INNER JOIN contacts z ON y.contact_id = z.id

WHERE
x.assigned_user_id = '7077c616-4f2c-f966-d9b6-5581807bae86'
AND z.deleted = 1
;

Finally I'm going to assign all the Contacts related to the Accounts to this employee based on the comments explained on my previous post.

UPDATE
accounts h
INNER JOIN accounts_contacts i ON h.id = i.account_id
INNER JOIN contacts j ON i.contact_id = j.id

SET j.assigned_user_id = '7077c616-4f2c-f966-d9b6-5581807bae86'

WHERE
h.assigned_user_id = '7077c616-4f2c-f966-d9b6-5581807bae86';

 

 

 

 

 

 

 

Leave a Reply

Your email address will not be published. Required fields are marked *