更新交集表,替代删除->插入
我希望更多 SQL 方面的知识可以帮助我。假设下表和它们的关系被压缩。
orders(PK_refno,customer, status)
order_accessories(PK_refno,PK_acc)
accessories(PK_acc,name,desc)
正如您所看到的,这是一个典型的 1:*----*:*----*:1
场景,问题或我担心的是更新时,因为每个订单都有配件可以修改,这意味着用户可以添加/删除配件。
我想到使用 MySQL 来做到这一点的唯一方法是删除所有附件,然后插入更新的附件。
我不喜欢这种方式。因为我认为可能有一种 SQL 方式可以做到这一点。也许有人可以建议和高级查询(我当然会研究)
我想的另一种方法是:
我也不喜欢这个,因为它是在应用程序中完成的,而不是在数据库中。
I hope some one more SQL wise can help me. Suppose the following table and relationships they're compacted.
orders(PK_refno,customer, status)
order_accessories(PK_refno,PK_acc)
accessories(PK_acc,name,desc)
As you can see this is a typical 1:*----*:*----*:1
scenario the the issue or my concern is when updating, as the accessories that each order has can be modified, meaning that an user can add/remove accessories.
The only way I've thought to do it by using MySQL is to delete all accessories and then insert the updated ones.
I dislike it this way. As I think that probably there's a SQL way to do it. Maybe someone can suggest and advanced query (which I'll study of course)
The other way I thought was to:
I'm not a fan of this either because it would be done in the app, not in the database.
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(2)
假设表格是这样开始的。
我们还假设 75 应该是 76。假设有一个正常的用户界面,用户只需将 75 更改为 76。一个正常的用户界面会将此语句发送到 dbms。
如果 75 一开始就不应该存在,那么用户只需删除该行即可。正常的用户界面会将此语句发送到 dbms。
Let's say the table starts like this.
Let's also say that 75 is supposed to be 76. Assuming a sane user interface, the user can just change 75 to 76. A sane user interface would send this statement to the dbms.
If 75 were not supposed to be there in the first place, then the user would just delete that one row. A sane user interface would send this statement to the dbms.
我只想补充一点,我首先删除了与要更新的顺序匹配的所有记录,然后重新插入新记录。
I just want to add that I went for first deleting all of the records that matched the order to be updated and then re-inserted the new ones.