Updating intersection tables, alternative to delete->insert
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:
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.
加载中,请稍侯......
精彩评论