How to perform edit in junction table

Question

What is the best practice to make edit in junction tables?

Items{ItemId, Name, Price...}
Shops{ShopId, Name, Address...}
ItemsInShops{ItemId, ShopId, DeliveryDate...}

Now I have 30 items in one shop. I want to edit that list and I uncheck 10 items and check 50 new items.

I do this in the following way: Remove all rows from 'ItemsInShops' by 'ItemId' and add new values. I don't think that this is good solution. Is there any better way to do this kind of update?

Maybe I didn't express problem with good example. Take a look at this:

User{UserId, Username, Password...}
Roles{RoleId, Name, Description} // Admin, Member, Superuser, Junior etc
UsersInRoles{UserId,RoleId}

User can have any number of roles.

John > Admin, Member, Superuser

That is three rows in junction table 'UserInRoles'. If I want to update this user to have the following roles:

John > Member, Junior

Now I do this update on database in the following way: I remove all John roles from 'UserInRoles' table and add new data. I don't know is there any better way to do this update, other than delete all and insert new? What if update fails from some reason (lost internet connection for example)?

Answer 1

Based on the comments, there is no better way to add/remove the database data, than to add/remove the database data.

Alternatively, you could have columns in the database for status information, e.g. "paid" "sold" "date delivered" "cancelled" etc. Then instead of deleting records, you would update the status. That makes it easier to check past transactions and keep records correct (e.g. "uncancel").

Answer 2

Your updated question is interesting.

efficiency wise, removing then adding the data could be done with MERGE.

Regarding the two queries running in a row, when they really form one process, you need transactions. They are "all or nothing" settings so that a set of queries either all succeed, or all fail (are all "rolled back"). Transactions are very useful, e.g. "user pays + product is delivered" or "past data is archived + deleted", and they are discussed in detail in many textbooks and websites.

Answer 3

You might look in BOOKS Online at the MERGE keyword.

Answer 4

I don't know is there any better way to do this update, other than delete all and insert new?

You don't have to delete all the rows to start with.

You can delete only the rows that no longer apply, and insert only the rows that are new. Or you can update a value that no longer applies with a value that does apply.

So to get from this

Name    Role
--
John    Admin
John    Member
John    Superuser

to this

Name    Role
--
John    Member
John    Junior

You can delete what no longer applies . . .

delete from userinroles
where Name = 'John' 
  and (Role = 'Admin' or Role = 'Superuser');

and insert what does apply.

insert into userinroles (Name, Role)
values ('John', 'Junior');

Or you can update a value with a new value.

delete from userinroles
where Name = 'John' 
  and Role = 'Admin';

Followed by

update userinroles
set Role = 'Junior'
where 'Name' = 'John' and Role = 'Superuser';

You said

What if update fails from some reason (lost internet connection for example)?

That's what transactions are for. Multiple statements within a single SQL transaction are all or nothing--either they all succeed, or no changes are made.