如何在接线表中执行编辑 [英] How to perform edit in junction table

问题描述

在连接表中编辑的最佳做法是什么？

 项目{ItemId，Name，Price ...} 
商店{ShopId，Name，Address ...} 
 ItemsInShops {ItemId，ShopId，DeliveryDate ...} 
  

现在我在一家商店有30件商品。我想编辑该列表，我取消选择10个项目，并检查50个新项目。

我以下列方式执行此操作：从ItemsInShops中的ItemId删除所有行'并添加新值。我不认为这是很好的解决方案。有没有更好的方式来做这种更新？

也许我没有表达问题的好例子。看看这个：

 用户{UserId，用户名，密码...} 
角色{RoleId，Name ，描述} // Admin，Member，Superuser，Junior etc 
 UsersInRoles {UserId，RoleId} 
  

用户可以有任意数量的角色。

  John>管理员，会员，超级用户
  

这是连接表UserInRoles中的三行。
如果我想更新此用户具有以下角色：

  John>会员，初级
  

现在我以下列方式对数据库进行此更新：
我删除来自UserInRoles表的所有约翰角色，并添加新数据。我不知道有没有更好的方法来做这个更新，除了删除所有并插入新的？如果更新由于某些原因（例如丢失的互联网连接）而失败？

解决方案

有没有更好的方法
做这个更新，除了删除
全部并插入新的？

您不必删除所有的行以开始。

您只能删除不再适用的行，并只插入新的行。或者您可以更新不再适用于适用的值的值。

所以从这个

 名称角色
  -  
 John Admin 
 John Member 
 John Superuser 
  pre> 
 
 
到此
 名称角色
  - 
 John Member 
 John Junior 
  
您可以删除不再适用的内容。 。 
  delete from userinroles 
其中Name ='John'
和（Role ='Admin'或Role ='Superuser'）; 
  
并插入适用的内容。
  insert into userinroles（Name，Role）
 values（'John'，'Junior'）; 
  
或者您可以使用新值更新值。
  delete from userinroles 
其中Name ='John'
和Role ='Admin'; 
  
后跟
 code> update userinroles 
 set Role ='Junior'
其中'Name'='John'和Role ='Superuser'; 
  
你说
 
 
如果更新由于某些原因导致
（
示例丢失互联网连接）会怎么样？
 
 
什么交易用于。单个SQL事务中的多个语句全部或全部，无论是全部成功还是不作更改。
 
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)?
解决方案

  
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.

                        
这篇关于如何在接线表中执行编辑的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！