sql server 关于更新表的理论
我有一个 ASP.NET Web 应用程序，用户可以在其中更新数据库中的表。我想知道执行此操作的正确方法。如下图所示，红色阴影区域是我不需要需要更新的共同数据。
以下是我需要采取的步骤：
- 如果数据存在，则不要更新数据
- 删除数据不在用户更新中
- 从用户更新中添加尚未在表中的数据
我还需要审核此表，因此我将在其上有一个触发器。我想知道理论上对表进行这种更新的最佳方法是什么？
感谢 Aaron 介绍了合并的概念。
表数据如下所示：
foreignkey model primarykey
1 AA 1
1 AA1 2
1 AA3 3
23 B 4
22 C 5
用户将添加如下所示的数据：
foreignkey model primarykey
1 A1 1
1 AA1 2
1 AA3 3
22 C 5
如您所见，第一行已更新，倒数第二行已完全删除。你能帮我处理这个合并声明吗？
I have an asp.net web app where a user can update a table in a database. I would like to know the proper method of doing this. As you can see in the image below, the red shaded area is the data that I have in common that I do not need to update.
Here are the steps that I need to take:
- Don't update data if it exists
- Delete data that is not in the user update
- Add data from the user update that is not already in the table
I need to audit this table also, so I will have a trigger on it. I would like to know in theory what is the best way to do this kind of update to a table?
thank you to Aaron who introduced the concept of merge.
Here is what the table data looks like:
foreignkey model primarykey
1 AA 1
1 AA1 2
1 AA3 3
23 B 4
22 C 5
The user will be adding data that looks like this:
foreignkey model primarykey
1 A1 1
1 AA1 2
1 AA3 3
22 C 5
As you can see, the first row was updated, and the second to last row was deleted entirely. Can you please help me with this merge statement?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
是的，这是可能的，这就是所谓的合并。请参阅 SQL Server MERGE 语句的文档。
如果您不想在数据已存在的情况下执行任何更新，则只需不要添加
WHEN MATCHED子句即可。使用WHEN NOT MATCHED BY SOURCE和WHEN NOT MATCHED BY TARGET插入/删除丢失/删除的行。Yes, it's possible and it's called a merge. See the documentation for the SQL Server MERGE Statement.
If you don't want to perform any updates if the data already exists, then just don't add a
WHEN MATCHEDclause. USEWHEN NOT MATCHED BY SOURCEandWHEN NOT MATCHED BY TARGETto insert/delete the missing/removed rows.