更新SQL“关系"表的有效方法 [英] Efficient way to update SQL 'relationship' table

问题描述

假设我有三个正确规范化的表.一个人，一个资格和一个将人映射到资格:

Say I have three properly normalised tables. One of people, one of qualifications and one mapping people to qualifications:

人物:

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

那么我必须通过我的网络界面更新这个映射.(我犯了一个错误.Bob 获得了学士学位，而不是博士学位，而 Alice 刚刚获得了工程学士学位.)

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:

• 之前是真的，现在应该是假的
• 之前是假的，现在应该是真的
• 以前是真的，现在应该是真的
• 之前是假的，现在应该是假的

我不想做的是从四个复选框中读取值，然后点击数据库四次说Bob 以前有 BA 吗?现在他有."鲍勃以前有博士学位吗?因为他没有了"等等.

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.

更新 1:onedaywhen 提出了我遇到的同样的事情——只需删除所有旧条目，无论正确与否，然后插入新条目.

UPDATE 1: onedaywhen suggests the same thing which occurred to me -- simply delete all the old entries, correct or not, and INSERT new ones.

更新 2:potatopeelings 建议在表单中添加一些代码，用于存储可与提交时的新值进行比较的字段的原始值.

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.

推荐答案

从逻辑上讲，UPDATE 是一个 DELETE 后跟一个 INSERT (考虑到 SQL Server 触发器可以访问名为 inserted 和 deleted 的逻辑表，但没有 updated 表).因此，您应该只能访问数据库两次，即第一次 DELETE Bob 的所有行(正确或其他)，第二个 INSERT Bob 的所有正确行.

Logically, an UPDATE is a DELETE followed by an INSERT (consider that SQL Server triggers can access logical tables named inserted and deleted but there is no updated table). So you should be able to hit the database only twice i.e. first DELETE all rows (correct or otherwise) for Bob, second INSERT all correct rows for Bob.

如果您只想访问数据库一次，请考虑使用标准 SQL 的 MERGE，假设您的 DBMS 支持它(SQL Server 在 2008 年引入了它).

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).

这篇关于更新SQL“关系"表的有效方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！