mysql - SQL update on duplicate key update -

mysql - SQL update on duplicate key update -

i have asked question grouping , update big database table, didn't reply this.

i have table: name, date, detail, no, , name, date, detail pk.

somehow need update detail, , possible there duplicate key. need sum no duplicate rows. on duplicate key update used insert. how address problem?

first things first, multi-column primary key bad idea; you've found out, makes hard manipulate individual fields. ought add together autoincrement bigint column table, become new primary key, , three-column uniqueness constraint can unique index instead. should perform better... it'll allow sort of manipulation need, well. it'll allow perform modifications still allow identify original rows integer index.

if that, "one-time update" can done safely, long don't mind creating temporary tables work with. this:

create couple of temporary tables same schema, without unique three-column index - can have non-unique index, because it'll help queries you're perform;

copy records need process first table (including unique integer primary key);

update detail columns need update in temporary table;

use insert ... select sum , group by merge records sec table;

insert temp2 (...whatever...) select ...whatever..., sum(no) temp1 grouping ...whatever...

finally, delete records in temp1 table original table (using integer primary key), , insert records in temp2 table original table.

mysql sql database-design