CODEWITHSUNDEEP
sqloracle-databasemerge
DFL
DFL

Reputation: 173

Oracle SQL "deep update"

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.

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

  1. All the Items in the Bag are already in the database. In this case, I just need to update the Items in the database.
  2. There are Items in the Bag that are not in the database. In this case, I need to figure out which Items are new and then insert them
  3. There are Items in the database that are no longer in the Bag object I am updating. In this case, I need to delete the extra Items from the database.

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.

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?

Upvotes: 0

Views: 77

Answers (1)

vav
vav

Reputation: 4684

MERGE is a way to go.

There is a Bag: items=Item1, Item2

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

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

1st step (join):

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

it will give you

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

2nd step (merge)

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>

Upvotes: 1

Related Questions