Merge records in mysql

Question

I need to merge 2 records into one.

Mine table:

 ╔═════╦═══════╦═════╦═══════╗
 ║ id* ║ name  ║price║ rank  ║
 ╠═════╬═══════╬═════╬═══════╣
 ║  0  ║   A   ║ 12  ║  CAP  ║
 ║  1  ║   B   ║ 32  ║  BAC  ║
 ║  2  ║   B   ║ 13  ║  BAC  ║
 ╚═════╩═══════╩═════╩═══════╝

and after merge (same table NOT SELECT)

 ╔═════╦═══════╦═════╦═══════╗
 ║ id* ║ name  ║price║ rank  ║
 ╠═════╬═══════╬═════╬═══════╣
 ║  0  ║   A   ║ 12  ║  CAP  ║
 ║  1  ║   B   ║ 45  ║  BAC  ║
 ╚═════╩═══════╩═════╩═══════╝

looking for records that name, and rank is the same like one another and SUM price.

Merge database, and REMOVE second duplicated record.

I need to make it using Join statement or is there any faster method to do this?

hint : `sum() and group by` – Abhik Chakraborty May 11 '15 at 10:18 — Abhik Chakraborty, May 11 '15 at 10:18
Why is id '1' and not '2'? – Strawberry May 11 '15 at 10:30 — Strawberry, May 11 '15 at 10:30
Merge 2 to 1 co id stay at 1? – Rafał Figura May 11 '15 at 10:36 — Rafał Figura, May 11 '15 at 10:36

Jur Clerkx · Answer 1 · 2015-05-11T10:28:24.727

0

SELECT name,   
SUM (price), rank
FROM tablename   
GROUP BY name, rank;

This query provides you the information needed. For more info check: http://www.w3resource.com/sql/aggregate-functions/sum-with-group-by.php

edited May 11 '15 at 10:28

answered May 11 '15 at 10:21

Jur Clerkx

698
4
14

see requirement again, you lack rank column – Anonymous Duck May 11 '15 at 10:22
What about grouping up other values like date and string? Im getting null or 0 values instead of grouped one. – Rafał Figura May 11 '15 at 10:23

score 0 · Answer 2 · answered May 11 '15 at 10:22

0

Try this:

SELECT MIN(id),name,sum(price) as price,rank,   
SUM (price)   
FROM tablename   
GROUP BY name, rank;

answered May 11 '15 at 10:22

Sashi Kant

13,277
9
44
71

Select is quite easy, but how can i remove those duplicated records? – Rafał Figura May 11 '15 at 12:33

score 0 · Answer 3 · edited May 23 '17 at 12:22

Try this,

select name,
           SUM(price),
          @rownum := @rownum + 1 AS rank
    from your_table
    cross join (select @rownum := 0) r
    group by name

Counter for rank came from this post : select increment counter in mysql

Edit : I tried it using SQL fiddle Schema :

CREATE TABLE name_table
(
id int,
name varchar(5),
price decimal(10),
rank int
);

insert into name_table values(0, 'A', 12, 1);
insert into name_table values(1, 'B', 32, 2);
insert into name_table values(2, 'B', 13, 2);

Query :

select    id,
          name,
          SUM(price),
          @rownum := @rownum + 1 AS rank
    from name_table
    cross join (select @rownum := 0) r
    group by name

Result :

id  name price rank
0   A     12    1
1   B     45    2

Ok, but how can I remove that second record? I don't wanted to select them but make first table looks like second — Rafał Figura, May 11 '15 at 12:20

score 0 · Answer 4 · answered May 11 '15 at 10:39

0

Just try this

I think below is a way by which you can achieve your output

SELECT name, SUM (price), rank FROM `yourtablename` GROUP BY name, rank;

answered May 11 '15 at 10:39

Nikunj Soni

854
1
5
18

Select is quite easy, but how can i remove those duplicated records? – Rafał Figura May 11 '15 at 12:33

Strawberry · Answer 5 · 2015-05-11T11:01:12.160

0

This appears to be what you're after...

SELECT MIN(id) id 
     , name
     , SUM(price) price 
     , rank 
  FROM my_table 
 GROUP 
    BY name
     , rank;

edited May 11 '15 at 11:01

answered May 11 '15 at 10:43

Strawberry

33,750
13
40
57

Therw will be no changes because rank is other in all tables – Rafał Figura May 11 '15 at 10:48
0/A/12/1 9/B/35/3 That 9 id may be other, I don' care. I need to remove that second duplicated record from database – Rafał Figura May 11 '15 at 10:58
Edited question. I need edit database, not select value – Rafał Figura May 11 '15 at 12:40
That is two operations: 1 Update and 1 Delete - or you can create a new table from the query result. – Strawberry May 11 '15 at 12:45
I can't, There is to much data to do this – Rafał Figura May 11 '15 at 12:52

Merge records in mysql

5 Answers5