2

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?

Fluffy
  • 21
  • 1

2 Answers2

2

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?)

Community
  • 1
  • 1
David Fullerton
  • 3,224
  • 27
  • 38
  • Oracle directly supports upsert with the MERGE statement. You will have to make a `select :v1, :v2, :v3 from dual` as the row you're merging into the table. – Adam Musch Apr 24 '11 at 01:10
0

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.

Tony Casale
  • 1,537
  • 8
  • 7
  • I've done it once but I think it's not good for performance reasons. I'll have number of additional inserts, that's slower than select queries over indexed columns. Is't it? – Fluffy Apr 21 '11 at 18:02
  • This usually isn't a problem unless you have expensive check constraints. That's not to say it's the best way. – tster Apr 21 '11 at 18:08