CODEWITHSUNDEEP
mysqlselect
Jose Cabrera Zuniga
Jose Cabrera Zuniga

Reputation: 2619

Merging different rows in only one

I have a table like:

GymMachine   UsedHours  Building Section
   M1          5            A 
   M2          6            B
   N1          3            A

After some time someone noticed that M1 and N1 referred to the same machine (and this is happening with other machines) and now I need to find those rows that refer to the same machine and "merge" those rows in just one. Using mysql, for those rows that represent the same machine I need to add their respective UsedHours and keep just one of the rows. Any ideas, please?

Thanks

Upvotes: 1

Views: 28

Answers (1)

Schwern
Schwern

Reputation: 164679

Let's say we want to merge M1 into N1 and keep M1, and assuming GymMachine is unique...

UPDATE machines
SET UsedHours = UsedHours + (SELECT UsedHours FROM machines WHERE GymMachine = 'N1')
WHERE GymMachine = 'M1';

There might be a more elegant way to do it, but a subquery works.

Then delete N1.

DELETE FROM machines WHERE GymMachine = 'N1';

Upvotes: 1

Related Questions