多对多粗加工
我目前正在为一个项目实施 CRUD。我有很多多对多关系。其中一些是 table1_id、table2_id，有些还有附加列（价格、金额等）。
更新多对多表的最佳方法是什么：如您所见，可能已经有一些信息和新数据一起存在。
示例
t1_id t2_id amount
1 3 15
2 4 50
我想更新此信息并发送一些数据，例如：{1,3,15}、{2,5, 25}。所以我需要更新第一行并插入新行。 我想创建一个函数（pl/sql），它将获取记录表并检查它是新记录还是要更新的记录。
问题：有没有更好的方法来实现这个？有什么模式可以实现这个目标吗？
I'm currently implementing CRUD for a project. I have a lot of many-to-many relationships. Some of them are table1_id, table2_id, and some of them have additional columns (price, amount, etc).
What is the best way to update many-to-many table: as you can see, there can be some information already along with new data.
Example
t1_id t2_id amount
1 3 15
2 4 50
I'd like to update this info and send some data like : {1,3,15}, {2,5, 25}. So i need to update the first line and insert a new line.
I want to create a function (pl/sql) that will take table of records and check whether it's a new record or record for update.
Question: Are there any better ways to implement this? Are there any patterns to accomplish that goal?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
您正在寻找的模式通常称为“UPSERT”：如果行不存在则插入一行，否则更新现有行。您仍然需要循环遍历行，但可以为每一行执行一条语句。
我不确定 pl/sql 但这个问题可能会有所帮助： Oracle：如何 UPSERT（更新或插入表？）
The pattern you're looking for is usually called an "UPSERT": insert a row if one doesn't exist, otherwise update the existing row. You'll still need to loop through your rows, but you can execute a single statement for each row.
I'm not sure about pl/sql but this question might help: Oracle: how to UPSERT (update or insert into a table?)
我几乎总是懒惰地这样做，只是从交叉引用表中删除所有关联的行，然后运行插入语句将其链接回来。理想情况下，这一切都在事务中完成。我在性能分析方面没有做太多工作，但这样做可能并不算太糟糕，特别是当您需要进行大量更新时。
I almost always do this lazily and just delete all the associated rows from the cross-reference table, then run an insert statement to link it back up. This would ideally all be done within a transaction. I haven't done much in terms of analyzing the performance, but it probably isn't too bad to do it that way, especially if you have a large number of updates to make.