更新 SQL“关系”的有效方法桌子
假设我有三个正确标准化的表。一个人，一个资格，一个将人映射到资格：
人：
id | Name
----------
1 | Alice
2 | Bob
学位：
id | Name
---------
1 | PhD
2 | MA
人到学位：
person_id | degree_id
---------------------
1 | 2 # Alice has an MA
2 | 1 # Bob has a PhD
那么我必须通过我的 Web 界面更新此映射。 （我犯了一个错误。鲍勃拥有学士学位，而不是博士学位，而爱丽丝刚刚获得工程学学士学位。）
这些一对多关系映射有四种可能的状态：
- 以前为真，现在应该为假
- ，以前为假，现在应该是 true
- 之前是 true，应该保持 true
- 之前是 false，应该保持 false
我不想做的是从四个复选框中读取值，然后点击数据库四次说“Bob 之前有 BA 吗？” ？好吧，他现在做到了。” “鲍勃以前有过博士学位吗？因为他不再有了”等等。
其他人如何解决这个问题？
我很好奇是否其他人也能达到与我相同的解决方案。
更新1：onedaywhen提出了与我发生过的同样的事情——只需删除所有旧条目，无论正确与否，然后插入新条目。
更新2：potatopeelings建议在表单中添加一些代码，用于存储字段的原始值，该值可以与提交时的新值进行比较。
Say I have three properly normalised tables. One of people, one of qualifications and one mapping people to qualifications:
People:
id | Name
----------
1 | Alice
2 | Bob
Degrees:
id | Name
---------
1 | PhD
2 | MA
People-to-degrees:
person_id | degree_id
---------------------
1 | 2 # Alice has an MA
2 | 1 # Bob has a PhD
So then I have to update this mapping via my web interface. (I made a mistake. Bob has a BA, not a PhD, and Alice just got her B Eng.)
There are four possible states of these one-to-many relationship mappings:
- was true before, should now be false
- was false before, should now be true
- was true before, should remain true
- was false before, should remain false
what I don't want to do is read the values from four checkboxes, then hit the database four times to say "Did Bob have a BA before? Well he does now." "Did Bob have PhD before? Because he doesn't any more" and so on.
How do other people address this issue?
I'm curious to see if someone else arrives at the same solution I did.
UPDATE 1: onedaywhen suggests the same thing which occurred to me -- simply delete all the old entries, correct or not, and INSERT new ones.
UPDATE 2: potatopeelings suggests adding some code to the form which stores the original value of the field which can be compared with the new value on submit.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
从逻辑上讲，
UPDATE是DELETE后跟INSERT（考虑 SQL Server 触发器可以访问名为inserted的逻辑表） > 和已删除，但没有更新表）。因此，您应该只能访问数据库两次，即第一次删除Bob 的所有行（正确或其他），第二次INSERTBob 的所有正确行。如果您只想访问数据库一次，请考虑使用标准 SQL 的 MERGE，假设您的 DBMS 支持它（SQL Server 在 2008 年引入了它）。
Logically, an
UPDATEis aDELETEfollowed by anINSERT(consider that SQL Server triggers can access logical tables namedinsertedanddeletedbut there is noupdatedtable). So you should be able to hit the database only twice i.e. firstDELETEall rows (correct or otherwise) for Bob, secondINSERTall correct rows for Bob.If you want to hit the database only once, consider using Standard SQL's
MERGE, assuming your DBMS supports it (SQL Server introduced it in 2008).假设 UI 是一个复选框网格（1. 在问题中的 Ismail 评论中），
其中 x 代表复选框。我会使用前端脚本仅将更改发送回服务器。然后在单个事务下的人员到学位中进行插入和删除，或者合并（如 Ismail 的链接中指出的），
您将传递插入（和删除）查询人员 ID、学位 ID 对等列表。对于您的示例，INSERT 查询将是单对 (2,2)，而对于 DELETE 查询将是单对 (2,1)。
Assuming the UI is a checkbox grid (1. in Ismail comment in the question)
where the x represents checked boxes. I'd go with using the front-end script to send only the changes back to the server. Then doing the INSERTs and DELETEs in the People-to-degrees under a single transaction, or a MERGE (as pointed out in Ismail's link)
You would pass the INSERT (and DELETE) query a list of people ID, degree ID pairs like. For your example, the INSERT query would be the single pair (2,2) and for the DELETE query the single pair (2,1).