MySQL - Merge rows in table based on multiple criteria

Question

I'd like to merge rows based on multiple criteria, essentially removing duplicates where I get to define what "duplicate" means. Here is an example table:

     ╔═════╦═══════╦═════╦═══════╗
     ║ id* ║ name  ║ age ║ grade ║
     ╠═════╬═══════╬═════╬═══════╣
     ║  1  ║ John  ║ 11  ║   5   ║
     ║  2  ║ John  ║ 11  ║   5   ║
     ║  3  ║ John  ║ 11  ║   6   ║
     ║  4  ║ Sam   ║ 14  ║   7   ║
     ║  5  ║ Sam   ║ 14  ║   7   ║
     ╚═════╩═══════╩═════╩═══════╝

In my example, let's say I want to merge on name and age but ignore grade. The result should be:

     ╔═════╦═══════╦═════╦═══════╗
     ║ id* ║ name  ║ age ║ grade ║
     ╠═════╬═══════╬═════╬═══════╣
     ║  1  ║ John  ║ 11  ║   5   ║
     ║  3  ║ John  ║ 11  ║   6   ║
     ║  4  ║ Sam   ║ 14  ║   7   ║
     ╚═════╩═══════╩═════╩═══════╝

I don't particularly care if the id column is updated to be incremental, but I suppose that would be nice.

Can I do this in MySQL?

You're probably better off dumping the result into a temp table (based on that one answer down there), and then truncate/dump this data back in. — durbnpoisn
– durbnpoisn, Commented Sep 10, 2015 at 19:57

durbnpoisn · Accepted Answer · 2015-09-10 20:00:40Z

1

My suggestion, based on my above comment.

SELECT distinct name, age, grade 
into tempTable
from theTable

This will ignore the IDs and give you only a distinct dump, and into a new table.

Then you can either drop the old and, and rename the new one. Or truncate the old one, and dump this back in.

answered Sep 10, 2015 at 20:00

durbnpoisn

4,6792 gold badges19 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

jds Over a year ago

Conceptually, this makes sense. I've never used INTO. Does tempTable have to exist before you run that command? When I try it, I get Undeclared variable: tempTable .

durbnpoisn Over a year ago

No. In fact, the table should not exist first. This will create the thing for you. The column definitions will be created automatically based on the columns you're using to create it. Depending on your database, you may need to name your table with a schema, like "dbo.tempTable".

jds Over a year ago

Okay, maybe this explains it: stackoverflow.com/questions/2949653/…. I'm using MariaDB.

jds Over a year ago

I didn't realize there was a difference. I've edited my question to remove references to SQL and will propose an edit to your answer.

durbnpoisn Over a year ago

Well, the question was already plussed up because it seems it's helpful. And the comments here show how we arrived at a solution - up to and including two different methods (in syntax). So, there is no need to edit. In any case - glad to be of help...

zedfoxus · Accepted Answer · 2015-09-10 20:28:18Z

1

You could just delete the duplicates in place like this:

delete test
from test 
inner join (
  select name, age, grade, min(id) as minid, count(*)
  from test
  group by name, age, grade
  having count(*) > 1
) main on test.id = main.minid;

Example: http://sqlfiddle.com/#!9/f1a38/1

answered Sep 10, 2015 at 20:28

zedfoxus

37.4k5 gold badges68 silver badges66 bronze badges

Collectives™ on Stack Overflow

MySQL - Merge rows in table based on multiple criteria

2 Answers 2

5 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related