mysql - inserting and deleting rows in the most optimal way -

i have existing table

id, name, user 15, bob, 1 25, alice, 2 30, ann, 1 55, bob, 2 66, candy, 1 

we want name records user 1 set values in string:

"ann, candy, dave" 

if easy way

delete table user = 1 insert table (name,user) values (ann,1), (candy,1), (dave,1)` 

then table looks this

id, name, user 25, alice, 2 55, bob, 2 67, ann, 1 68, candy, 1 69, dave, 1 

i.e. new rows created. don't want new identities, , on time in huge tables, causes fragmentation , identity holes , on. efficient way in sql reduce actual 2 required operations:

delete table user = 1 , name not in string "ann, candy, dave", table then:

25, alice, 2 30, ann, 1 

55, bob, 2 66, candy, 1 `

insert table user = 1, name = value "ann, candy, dave" not match name/user=1 , table then: 25, alice, 2 30, ann, 1 55, bob, 2 66, candy, 1 67, dave, 1

it sounds have list , want process twice, once deletes , once inserts. store list in temporary table , use processing.

along way, start unique index on user, name prevent updates table:

create unique index idx_table_user_name on table(user, name); 

this seems requirement data, let database enforce it. code processing like:

create temporary table toprocess (      select 1 user, 'ann' name union      select 1, 'candy' union      select 1, 'dave'     );  create index idx_toprocess_user_name on toprocess(user, name);  delete t     table t     t.user in (select p.user toprocess p) ,           not exists (select 1 toprocess p p.user = t.user , p.name = t.name);  insert table(user, name)     select user, name     toprocess     on duplicate key update user = values(user); 

although might bit complicated, lets handle multiple users @ same time. and, list processing entered once, reduces scope error.