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 encountered an error and created duplicates in the contacts table (all data except the id are 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 gives me the latest client_id for each ticket (based on the from_email column in helpdesk_tickets):

SELECT from_address, MAX(contact_id) as a FROM helpdesk_tickets GROUP BY from_address HAVING COUNT(contact_id)>1;

Now I need to update every row with the same from_address to the maximum value selected above. How to achieve?

Answer 1

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;