efficent truncate method in mysql?

i want to sort my table in this manner. This is the current state of my table:

And this would be the outcome:

So basically in pseudo code it's:

IF SAME STRING IN "tag" COLUMN THEN add both "power" 

Can anyone suggest an开发者_运维问答 efficient way to achieve this if any?

select id, sum(power), tag from YourTabeName group by tag

If you need to query from it I would just make it a view.

Create a new table called t1_temp with the exact same structure as your original table called t1. The following should get the job done:

TRUNCATE t1_temp; INSERT INTO t1_temp (id, power, tag) VALUES (SELECT id, SUM(power), tag FROM t1 GROUP BY tag); RENAME TABLE t1 TO t1_temp2, t1_temp TO t1, t1_temp2 TO t1_temp

However, I will recommend that you try to modify your insert statement. Create a unique index on tag and then use something like the following (assuming that id is auto-increment)

INSERT INTO t1 (power, tag) VALUES (3, 'option1') ON DUPLICATE KEY UPDATE power = power + 3

Even though you've already solved this is another approach

update your_table as t1
inner join 
(select tag,sum(power) as power from your_table group by tag) as t2
set t1.power = t2.power
where t1.tag = t2.tag


alter ignore table your_table add unique index i (tag);