更新交集表,替代删除->插入

发布于 2024-10-19 00:50:50 字数 513 浏览 2 评论 0原文

我希望更多 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:

  • Retrieve the originals.
  • Compare them and remove/add the differences.

    I'm not a fan of this either because it would be done in the app, not in the database.

  • 如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

    扫码二维码加入Web技术交流群

    发布评论

    需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

    评论(2

    余罪 2024-10-26 00:50:50

    假设表格是这样开始的。

    order_accessories
    PK_refno  PK_acc
    1         73
    1         74
    1         75
    1         86
    1         92
    

    我们还假设 75 应该是 76。假设有一个正常的用户界面,用户只需将 75 更改为 76。一个正常的用户界面会将此语句发送到 dbms。

    update order_accessories
    set PK_acc = 76
    where (PK_refno = 1 and PK_acc = 75);
    

    如果 75 一开始就不应该存在,那么用户只需删除该行即可。正常的用户界面会将此语句发送到 dbms。

    delete from order_accessories
    where (PK_refno = 1 and PK_acc = 75);
    

    Let's say the table starts like this.

    order_accessories
    PK_refno  PK_acc
    1         73
    1         74
    1         75
    1         86
    1         92
    

    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.

    update order_accessories
    set PK_acc = 76
    where (PK_refno = 1 and PK_acc = 75);
    

    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.

    delete from order_accessories
    where (PK_refno = 1 and PK_acc = 75);
    
    酷到爆炸 2024-10-26 00:50:50

    我只想补充一点,我首先删除了与要更新的顺序匹配的所有记录,然后重新插入新记录。

    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.

    ~没有更多了~
    我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
    原文