Update the contact_id column of each row with the same from_address to the maximum value

Question

I have a database with a table called contacts that contains a list of customers, and a table called helpdesk_tickets where each row represents a help desk ticket from a customer. The software went wrong and created duplicates in the contacts table (all data except the id is the same). Actually, in the helpdesk_tickets table, some emails are assigned to different customer ids. I want all tickets with the same email address to be assigned to the latest client_id. this

P粉994092873 · Answer

You can use the update link here:

UPDATE helpdesk_tickets ht1
INNER JOIN
(
    SELECT from_address, MAX(contact_id) AS max_contact_id
    FROM helpdesk_tickets
    GROUP BY from_address
) ht2
    ON ht2.from_address = ht1.from_address
SET
    contact_id = ht2.max_contact_id
WHERE
    contact_id <> ht2.max_contact_id;