Geek Answers Handbook

Update and delete query to merge records

I have a table with the following entries

id | fname | lname | skills 

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

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

 id | fname | lname | skills 

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

the skill will be merged with the last id and other duplicate entries.

any help would be very helpful to me.

+4
source share
3 answers

Try the following:

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 the 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)

Removing will only work if IDunique! It will delete all entries that are not the largest.ID

EDIT: try the following:

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
+2
source

try it,

SELECT 
        MAX(id) AS id, fname, lname, 
        GROUP_CONCAT(DISTINCT(skills)) AS skills
FROM
        < your_table >
GROUP BY
        fname, lname

References:

http://www.w3resource.com/mysql/aggregate-functions-and-grouping/aggregate-functions-and-grouping-group_concat.php

http://dev.mysql.com/doc/refman/5.7/en/group-by-functions.html#function_group-concat

+1

Assuming you have a limit of 10 skills per row, you can normalize the column skillswith the following query:

select distinct 
    s.fname, s.lname, 
    replace(
        substring(substring_index(s.skills, ',', p.pos),
        char_length(substring_index(s.skills, ',', p.pos -1)) + 1),
        ',', ''
    ) as skill
from
    skills s
cross join 
    (select 1 pos 
    union all select 2 
    union all select 3 
    union all select 4 
    union all select 5 
    union all select 6 
    union all select 7 
    union all select 8 
    union all select 9 
    union all select 10
) p on p.pos <= (char_length(s.skills) - char_length(replace(s.skills, ',', ''))) + 1

Result:

| fname | lname |  skill |
|-------|-------|--------|
|  Jane |   Doe |    php |
|  Jane |   Doe |   java |
|  Jane |   Doe |  mongo |
|  Jane |   Doe |  mysql |
|  Jane |   Doe | oracle |

sqlfiddle

(char_length(s.skills) - char_length(replace(s.skills, ',', ''))) + 1

will return the number of skills (counting commas).

    replace(
        substring(substring_index(s.skills, ',', p.pos),
        char_length(substring_index(s.skills, ',', p.pos -1)) + 1),
        ',', ''
    )

will extract the skill in the given position.

Instead of creating a table of positions in a row, you can use any existing table with a continuous sequence of numbers.

To denormalize the result again, you can use GROUP_CONCAT(DISTINCT skill):

select 
    s.fname, s.lname, 
    group_concat(distinct replace(
        substring(substring_index(s.skills, ',', p.pos),
        char_length(substring_index(s.skills, ',', p.pos -1)) + 1),
        ',', ''
    )) as skills
from skills s
cross join (
    select 1 pos 
    union all select 2 
    union all select 3 
    union all select 4 
    union all select 5 
    union all select 6 
    union all select 7 
    union all select 8 
    union all select 9 
    union all select 10
) p on p.pos <= (char_length(s.skills) - char_length(replace(s.skills, ',', ''))) + 1
group by s.fname, s.lname

Result:

| fname | lname |                      skills |
|-------|-------|-----------------------------|
|  Jane |   Doe | php,mysql,mongo,oracle,java |

sqlfiddle

Thus, no skill appears twice in the list.

0
source

More articles:


All Articles