How to merge multiple rows in MySQL?

Question

I ran into some problems while structuring my database, and I will ask two questions.

First question: below table needs to be merged by the same IDs

 ╔═════╦═══════╦═════╦═══════╗
 ║ id* ║ name  ║ age ║ grade ║
 ╠═════╬═══════╬═════╬═══════╣
 ║  0  ║ John  ║     ║       ║
 ║  0  ║       ║ 11  ║       ║
 ║  0  ║       ║     ║   6   ║
 ║  1  ║ Dave  ║     ║       ║
 ║  1  ║       ║ 12  ║       ║
 ║  1  ║       ║     ║   7   ║
 ╚═════╩═══════╩═════╩═══════╝

so it should look like this;

 ╔═════╦═══════╦═════╦═══════╗
 ║ id* ║ name  ║ age ║ grade ║
 ╠═════╬═══════╬═════╬═══════╣
 ║  0  ║ John  ║ 11  ║   6   ║
 ║  0  ║ Dave  ║ 12  ║   7   ║
 ╚═════╩═══════╩═════╩═══════╝

NOTE: id* is not AUTO_INCREMENT

Second question: You probably think that the former database structure is poor. The good thing is, I haven't created the database yet and I have been looking for a solution to add data to an existing row without removing old information, but if there is no old information, it would create a new row. Thanks in advance.

Second question explained

Virgin table

 ╔═════╦═══════╦═════╦═══════╗
 ║ id* ║ name  ║ age ║ grade ║
 ╠═════╬═══════╬═════╬═══════╣
 ║     ║       ║     ║       ║
 ╚═════╩═══════╩═════╩═══════╝

some SQL statement

 ╔═════╦═══════╦═════╦═══════╗
 ║ id* ║ name  ║ age ║ grade ║
 ╠═════╬═══════╬═════╬═══════╣
 ║  0  ║ John  ║     ║       ║
 ╚═════╩═══════╩═════╩═══════╝

the same SQL statement with different parameters

 ╔═════╦═══════╦═════╦═══════╗
 ║ id* ║ name  ║ age ║ grade ║
 ╠═════╬═══════╬═════╬═══════╣
 ║  0  ║ John  ║     ║       ║
 ║  1  ║ Dave  ║     ║       ║
 ╚═════╩═══════╩═════╩═══════╝

another SQL statement

 ╔═════╦═══════╦═════╦═══════╗
 ║ id* ║ name  ║ age ║ grade ║
 ╠═════╬═══════╬═════╬═══════╣
 ║  0  ║ John  ║     ║       ║
 ║  1  ║ Dave  ║ 12  ║       ║
 ╚═════╩═══════╩═════╩═══════╝

another SQL statement

 ╔═════╦═══════╦═════╦═══════╗
 ║ id* ║ name  ║ age ║ grade ║
 ╠═════╬═══════╬═════╬═══════╣
 ║  0  ║ John  ║     ║   6   ║
 ║  1  ║ Dave  ║ 12  ║       ║
 ╚═════╩═══════╩═════╩═══════╝

... and so on.

Taryn · Accepted Answer · 2013-02-05 21:46:29Z

18

You should be able to apply an aggregate function to all the columns and then GROUP BY id:

select id,
  max(name) name,
  max(age) age,
  max(grade) grade
from yourtable
group by id

See SQL Fiddle with Demo

As far as the DB structure, the only issue that I see is that you are inserting multiple records for the same user. You should be using an UPDATE statement to use the values instead of inserting.

It sounds like you want to use the REPLACE function in MySQL (here is a tutorial).

So the query would be similar to this:

REPLACE 
  INTO yourtable (`id`, `name`, `age`, `grade`) 
  VALUES (0, 'john', 11, null);

See SQL Fiddle with Demo

edited Feb 5, 2013 at 21:46

answered Feb 5, 2013 at 21:22

Taryn

249k57 gold badges374 silver badges409 bronze badges

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

9 Comments

Berk Özbalcı Over a year ago

Thank you very much. By the way, what about my databate structure?

Taryn Over a year ago

Downvoter care to explain? Seriously I get a DV but the other answer doesn't

Taryn Over a year ago

@TheConjuring I don't understand your question about the database structure what are you asking?

Aaron Bertrand Over a year ago

@TheConjuring if you're trying to ask two different questions, please do so by asking two different questions rather than splintering this one.

Aaron Bertrand Over a year ago

@TheConjuring sorry, not familiar with the XY problem I guess.

|

Andomar · Accepted Answer · 2013-02-05 21:21:42Z

5

You could group by id, and use any aggregate function to pick the non-null row. This example uses max, but min would work too:

select  id
,       max(name) as name
,       max(age) as age
,       max(grade) as grade
from    YourTable
group by
        id

answered Feb 5, 2013 at 21:21

Andomar

239k55 gold badges387 silver badges412 bronze badges

Collectives™ on Stack Overflow

How to merge multiple rows in MySQL?

Second question explained

2 Answers 2

9 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

Second question explained

2 Answers 2

9 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related