VipinS VipinS - 6 months ago 14
MySQL Question

Update and Delete query for merging records

I have a table with following records

id | fname | lname | skills

22 | Jane | Doe | php,mysql
43 | Jane | Doe | java,oracle,php
45 | Jane | Doe | mongo,mysql


id is primary key .I want to execute a query so that in the table looks like this

id | fname | lname | skills

45 | Jane | Doe | php,mysql,java,oracle,mongo


skill will merged with latest id and other duplicate records deleted

any help will be very useful for me.

Answer

Try this:

UPDATE YourTable t
INNER JOIN(SELECT max(s.id) as max_id,s.fname,s.lname,GROUP_CONCAT(distinct(s.skills)) as skill_str
           FROM YourTable s
           GROUP BY s.fname,s.lname) t1
 ON (t.lname = t1.lname and t.fname = t1.fname AND t.id = t1.max_id)
SET t.skills = t1.skill_str

This will update the table to your desired concat, and then delete:

DELETE FROM YourTable t
WHERE t.ID NOT IN(SELECT MAX(s.id) FROM YourTable s
                  GROUP BY s.lname,s.fname)

The delete will work only if ID is unique! It will delete all records that are not the largest ID

EDIT: try this:

DELETE t
FROM candidate t
LEFT JOIN(SELECT MAX(s.candidate_id) as max_id FROM candidate s
          GROUP BY s.fname,s.lname) t1
 ON (t.candidate_id = t1.max_id)
WHERE t1.max_id is null