I really hope someone more SQL smart might help me. Imagine that the following table and associations they are compressed.


orders(PK_refno,customer, status)
order_accessories(PK_refno,PK_acc)
accessories(PK_acc,name,desc)


As you can tell this can be a typical 1:*----*:*----*:1 scenario the the problem or my problem is when upgrading, because the add-ons that every order has could be modified, and therefore an user can also addOreliminate add-ons.

The only method I have thought to get it done by utilizing MySQL would be to remove all add-ons after which place the up-to-date ones.

I dislike it by doing this. When I believe that most likely there is a SQL method of doing it. Maybe someone can suggest and advanced query (which I'll study obviously)

Another way I figured ended up being to:

  • Retrieve the originals.
  • Do a comparison and take awayOrinclude the variations.

    I am not keen on this either because it might be completed in the application, not within the database.

  • Among the finest to include which i went for first removing all the records that matched up an order to become up-to-date after which re-placed they.

    Let us the table begins such as this.

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

    Let us also state that 75 should be 76. Presuming a sane interface, the consumer can just change 75 to 76. A sane interface would send this statement towards the dbms.

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

    If 75 weren't said to be there to begin with, then your user would certainly remove that certain row. A sane interface would send this statement towards the dbms.

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