Delete duplicate records while updating fields to a specific record in MYSQL

Question

I have a table which has duplicate of entries by code_number.

Table: sheets

 id  |  code_number | animal     |  location
  1      001          cow           loc1
  2      002          dog           loc2
  3      003          goat          loc3
  4      003          sheep         loc4
  5      003          elephant      loc5
  6      002          bird          loc6

I am about to delete duplicate records and put all the group fields into a single entry.

Based on the table data above, my expected result is:

 code_number  |   animal              | location
    001       |     cow               |    loc1
    002       |  dog, bird            |   loc2, loc6, 
    003       | goat, sheep, elephant | loc3, loc4, loc5

I can only produce the deleting of duplicate entries and retain a single entry. But, I don't know how to update/copy the record into a single record while deleting the rest duplicate entries.

DELETE n1 FROM sheets n1, sheets n2
WHERE n1.id > n2.id AND n1.code_number = n2.code_number;

How can I do this in just a single query?

If this is not homework then simply don't do it: https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad — forpas, Jun 20 '22 at 15:13

Rahul Biswas · Accepted Answer · 2022-06-21T13:36:21.047

1

Try this below code for preparing comma separated value.

SELECT code_number,
       group_concat(animal) animal,
       group_concat(location) location
FROM sheets
GROUP BY code_number

Use DISTINCT keyword inside group_concat function if same value exists multiple time of a code number.

Insert this query result into a different tables.

edited Jun 21 '22 at 13:36

answered Jun 20 '22 at 15:10

Rahul Biswas

3,207
2
10
20

Delete duplicate records while updating fields to a specific record in MYSQL

1 Answers1