Oracle SQL“深度更新" [英] Oracle SQL "deep update"

问题描述

所以我不确定它叫什么，但我需要弄清楚如何最好地进行可能涉及插入，更新和删除操作的更新.情况如下:我的应用程序具有存储在数据库中的POJO.这些对象可以包括也存储在数据库中的其他对象的列表.例如，一个Bag对象可能具有Item对象的列表.

So I'm not sure what it's called, but I need to figure out how best to do an update that may involve insert, update, and delete operations. Here's the situation: My application has POJO's that are stored in the database. These objects may include lists of other objects that are also stored in the database. For example, a Bag object might have a list of Item objects.

我希望能够更新Bag.但是，有3种可能的情况:

I want to be able to update the Bag. However, there are 3 possible situations:

1. 包装袋中的所有物品已经在数据库中.在这种情况下，我只需要更新数据库中的项目.
2. 袋中有一些不在数据库中的项目.在这种情况下，我需要找出哪些项目是新的，然后将其插入
3. 我正在更新的Bag对象中不再有数据库中的项目.在这种情况下，我需要从数据库中删除多余的项目.

这里最明显的解决方案是删除它，然后重新插入所有内容.但这很慢，并且会导致生成新密钥.在大多数情况下，这不是问题，但可能会在将来引起重大问题.

The obvious solution here is to delete it an re-insert everything. But this is slow and will cause new keys to be generated. Most of the time it is not a problem, but it could potentially cause significant problems down the road.

从我的研究中，我发现合并可能会有用.但是，据我了解，MERGE对于数据组更好.我很可能只想对单个条目进行更新，或者项目"列表可能只包含一个对象.此外，MERGE似乎只能删除匹配的记录.

From my research, I've seen that a MERGE may be useful. However, from what I've read, MERGE's are better for groups of data. It is very likely that I might only want to do an update on a single entry or the list of Items may only contain a single object. In addition, it looks like MERGE can only delete records that were matched.

设计此功能的最佳方法是什么?

What is the best way to design this functionality?

推荐答案

MERGE是一种解决方法.

MERGE is a way to go.

有一个袋子: items = Item1，Item2

There is a Bag: items=Item1, Item2

有一个BagInDB: bag_id = 1 items = Item1，Item3

There is a BagInDB: bag_id = 1 items=Item1,Item3

所以我们需要更新Item1，添加Item2并删除Item3

So we need to update Item1, add Item2 and delete Item3

第一步(加入):

select * from bag full outer join (select * from bagInDB where bag_id = 1)

它会给你

bag_itemName bagInDb_itemName
------------ ----------------
Item1        Item1
Item2        null
null         Item3

第二步(合并)

merge into baginDB b
using(query above) q on b.bag_id = 1 and b.itemName = q.bagInDb_itemName
when matched then
delete where q.bag_itemName is null
<rest of the conditions>

这篇关于Oracle SQL“深度更新"的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！