What I generally do in these situations is to make the source - a join which includes the primary key of the target table, which can help guarantee uniqueness for the MERGE and still get the desired results. This frustrated the heck out of me at first because I was used to working with MERGE syntax from Oracle/SQL - which pretty much did that for you. Once I got the hang of it - it's not bad at all.
Hope that Helps, -Brian On May 3, 1:10 pm, SuperSpinner <[email protected]> wrote: > Hi Thomas, > Thanks for the reply. What I'm trying to accomplish is this: > While I didn't create primary key/foreign key relationships in the > example, my real life use case has them. > Given that test_a.id is a primary key and test_ab.aid is a foreign key > pointing to test_a.id, > I want to update the two rows in test_ab that point to the test_a row > where test_a = 11 > > It's possible to write a merge or insert/join type of query on both > oracle and sql server to do this, but I haven't been able to figure > out > how to write a query in H2 to accomplish the same thing. > > something like > update test_ab set test_ab.ab = test_ab.ab + 2000 from > test_a,test_ab where test_ab.aid = test_a.id and test_a.a=11 > > Is it possible? > > thanks > Randy > > On May 1, 6:06 am, Thomas Mueller <[email protected]> > wrote: > > > > > Hi, > > > > I want MERGE to update AB but it fails because > > > it apparently is trying to merge 2 rows into one row. > > > No, it is trying to merge one row into two rows: before merging, there > > are two rows in test_ab with aid = 1. Therefore, aid can't be used as > > the key. > > > Regards, > > Thomas > > > -- > > You received this message because you are subscribed to the Google Groups > > "H2 Database" group. > > To post to this group, send email to [email protected]. > > To unsubscribe from this group, send email to > > [email protected]. > > For more options, visit this group > > athttp://groups.google.com/group/h2-database?hl=en. > > -- > You received this message because you are subscribed to the Google Groups "H2 > Database" group. > To post to this group, send email to [email protected]. > To unsubscribe from this group, send email to > [email protected]. > For more options, visit this group > athttp://groups.google.com/group/h2-database?hl=en. -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/h2-database?hl=en.
