CODEWITHSUNDEEP
mysql
frumbert
frumbert

Reputation: 2427

inserting and deleting rows in the most optimal way

i have an existing table

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

we want the name records for user 1 to now be set to the values in this string:

"ann, candy, dave"

if I do it the easy way

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

then the table now looks like this

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

i.e. new rows are created. I don't want the new identities, and over time in huge tables, this causes fragmentation and identity holes and so on. what is the most efficient way in SQL to reduce this to just the actual 2 required operations:

delete from table where user = 1 and name is not in the string "ann, candy, dave", so that the table is then:

25, alice, 2
30, ann, 1

55, bob, 2 66, candy, 1 `

insert into table user = 1, name = any value from "ann, candy, dave" that does not match name/user=1 , so that the table is then: 25, alice, 2 30, ann, 1 55, bob, 2 66, candy, 1 67, dave, 1

Upvotes: 0

Views: 35

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270733

It sounds like you have a list and want to process it twice, once for deletes and once for inserts. Store the list in a temporary table and use that for processing.

Along the way, start with a unique index on user, name to prevent updates into the table:

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

This seems to be a requirement for your data, so let the database enforce it. Then the code for processing is like:

create temporary table toprocess as (
     select 1 as user, 'ann' as name union all
     select 1, 'candy' union all
     select 1, 'dave'
    );

create index idx_toprocess_user_name on toprocess(user, name);

delete t
    from table t
    where t.user in (select p.user from toprocess p) and
          not exists (select 1 from toprocess p where p.user = t.user and p.name = t.name);

insert into table(user, name)
    select user, name
    from toprocess
    on duplicate key update user = values(user);

Although this might look a bit complicated, it lets you handle multiple users at the same time. And, the list for processing is only entered once, which reduces the scope for error.

Upvotes: 1

Giorgi Nakeuri
Giorgi Nakeuri

Reputation: 35790

It is not so clear but may be this is what you want:

delete from table where user = 1 and name not in('ann', 'candy', 'dave')

insert into table
select * from(select 'ann' as name
              union all
              select 'candy'
              union all
              select 'dave') t
where t.name not in(select name from table where user = 1)

Upvotes: 0

Related Questions