CODEWITHSUNDEEP
mysql
Robert
Robert

Reputation: 457

MySQL take duplicate data and combine unique data

With my MySQL database, I want to take data from my temporary table and insert it into my main table, while removing any duplicate data but also taking into consideration the data I already have. This seems to require an update and/or an insert depending on what exists in "data_table" so I really have no idea how to write it or if it is even possible. If this isn't possible, I'd like to know how to accomplish this while not considering what is already in "data_table" which I would think is possible. Thank you for your help!

Existing data_table before running query:

data_table

+-----id-----+-----age-----+-----gender-----+-----color-----+
=============+==============+=================+================+
     1              5                m               pink,red,purple

data_table_temp

+-----id-----+-----age-----+-----gender-----+-----color-----+
=============+==============+=================+================+
     1              5                m               red
     2              5                m               blue
     3              5                m               red  
     4              5                m               orange
     5              6                m               red 
     6              6                m               green     
     7              6                m               blue

After query:

data_table

+-----id-----+-----age-----+-----gender-----+-----color-----+
=============+==============+=================+================+
     1              5                m               pink,red,purple,blue,orange
     2              6                m               red,green,blue

Upvotes: 0

Views: 67

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1271231

Here is an approach to this problem which turned out to be harder than I expected.

The idea is to concat the colors that don't match and put them together. There is a bit of a problem assigning ids. Getting the "2" for the second row is a problem, so this just assigned the id sequentially:

select @id := @id + 1 as id,
       coalesce(dt.age, dtt.age) as age,
       coalesce(dt.gender, dtt.gender) as age,
       concat_ws(dt.color,
                 group_concat(case when find_in_set(dtt.color, dt.color) > 0
                                   then dtt.color
                              end)
                )
from data_table_temp dtt left outer join
     data_table dt join
     on dt.age = dtt.age and
        dt.gender = dtt.gender cross join
     (select @id := 0) var
group by coalesce(dt.age, dtt.age), coalesce(dt.gender, dtt.gender);

Upvotes: 2

Noah
Noah

Reputation: 1857

MySQL doesn't have any string functions to (easily) split a delimited string (like data_table.color).

However, if you have all of the data in data_table_temp's format (one color per row), you can generate the desired results like this:

SELECT DISTINCT age, GROUP_CONCAT(DISTINCT color)
FROM table WHERE [condition]
GROUP BY age;

Optionally adding in gender, as necessary.

Apologies for the half-answer

Upvotes: 1

Related Questions