开发者

Updating intersection tables, alternative to delete->insert

开发者 https://www.devze.com 2023-02-12 18:12 出处:网络
I hope some one more SQL wise can help me. Suppose the following table and relationships they开发者_Go百科\'re compacted.

I hope some one more SQL wise can help me. Suppose the following table and relationships they开发者_Go百科'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.


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


    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.

  • 0

    精彩评论

    暂无评论...
    验证码 换一张
    取 消

    关注公众号