> I'm working on this (as part of the new multi-version storage backend).
Great, thanks Thomas! Am Sonntag, 2. September 2012 21:29:15 UTC+2 schrieb Thomas Mueller: > > Hi, > > I guess it is similar to how many applications work with files: save the > file as a temp file, rename the old to .bak, and so on. One of the problems > is that H2 doesn't support this yet in a transactional way. I'm working on > this (as part of the new multi-version storage backend). > > Regards, > Thomas > > > > On Sat, Sep 1, 2012 at 8:35 AM, Lukas Eder <[email protected]<javascript:> > > wrote: > >> I hadn't thought about this thoroughly, so you may be right, >> performance-wise. For referential integrity, though, there's still a >> difference between truncate+insert (keys lost, new keys generated), and >> merge with delete (keys maintained). In some way or the other, this clause >> would certainly add value. >> >> But probably, the use case is too rare to implement it immediately. It >> was more of a long-term idea. >> >> Cheers >> Lukas >> >> Am Freitag, 31. August 2012 18:16:01 UTC+2 schrieb Thomas Mueller: >>> >>> Hi, >>> >>> I'm not convinced this would be faster than just truncate+insert, as the >>> database would have to do quite a bit more internally. And it's really just >>> saving one SQL statement, right? So I'm not convinced it would be very >>> useful. >>> >>> Regards, >>> Thomas >>> >>> >>> On Fri, Aug 31, 2012 at 7:42 AM, Ryan How <[email protected]> wrote: >>> >>>> I find I do operations like this all the time. It is kind of like >>>> doing a synchronisation of data. So when importing data I don't need to >>>> clear the table then import it all again, I can update existing, add new >>>> and remove old. >>>> >>>> I usually like to know what was inserted, what was updated, what was >>>> left untouched and what was deleted. So generally I do it manually so I >>>> can >>>> count stats or take additional actions accordingly. >>>> >>>> But I'm sure it would be useful for a really quick way to sync data >>>> into a table and would be a nice addition to the merge function. >>>> >>>> Maybe add it to the super long roadmap? :) >>>> >>>> >>>> >>>> >>>> On 31/08/2012 1:27 AM, Lukas Eder wrote: >>>> >>>> Hello, >>>> >>>> Have you had time to think about this idea? >>>> >>>> Cheers >>>> Lukas >>>> >>>> Am Freitag, 6. Juli 2012 21:42:02 UTC+2 schrieb Lukas Eder: >>>>> >>>>> Hello, >>>>> >>>>> H2's MERGE INTO statement is quite elegant. While it isn't as powerful >>>>> as the SQL standard or MySQL's ON DUPLICATE KEY UPDATE clause, it is a >>>>> lot simpler and intuitive than others. I was wondering if you had >>>>> previously thought about adding an additional, optional DELETE flag. >>>>> Something along these lines: >>>>> >>>>> MERGE INTO target (id, t1, t2) >>>>> KEY (id) >>>>> WITH DELETE >>>>> SELECT id, s1, s2 FROM source >>>>> >>>>> The syntax is just an example. Of course, this clause would work with >>>>> both SELECT and VALUES variants of the MERGE statement. Oracle >>>>> supports such a clause, and I find it quite useful. See the Oracle >>>>> documentation for details: >>>>> http://docs.oracle.com/cd/**B283**59_01/server.111/b28286/**statem** >>>>> ents_9016.htm<http://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_9016.htm> >>>>> >>>>> >>>>> With a single statement, I could: >>>>> >>>>> - Select a source data set >>>>> - Insert all new records into a target table >>>>> - Update all existing records in the target table >>>>> - Delete all "obsolete" records from the target table >>>>> >>>>> This differs from a simple TRUNCATE+INSERT in the fact that updates >>>>> are possible, e.g. large updates with few additions and few removals. >>>>> What do you think? >>>>> >>>>> Cheers >>>>> Lukas >>>>> >>>> -- >>>> You received this message because you are subscribed to the Google >>>> Groups "H2 Database" group. >>>> To view this discussion on the web visit https://groups.google.com/d/** >>>> msg/h2-database/-/DOElI18ZVS8J<https://groups.google.com/d/msg/h2-database/-/DOElI18ZVS8J> >>>> **. >>>> To post to this group, send email to [email protected]. >>>> To unsubscribe from this group, send email to h2-database...@** >>>> googlegroups.com. >>>> >>>> For more options, visit this group at http://groups.google.com/** >>>> group/h2-database?hl=en<http://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 h2-database...@** >>>> googlegroups.com. >>>> >>>> For more options, visit this group at http://groups.google.com/** >>>> group/h2-database?hl=en<http://groups.google.com/group/h2-database?hl=en> >>>> . >>>> >>> >>> -- >> You received this message because you are subscribed to the Google Groups >> "H2 Database" group. >> To view this discussion on the web visit >> https://groups.google.com/d/msg/h2-database/-/AcrHsHDcKAkJ. >> >> To post to this group, send email to [email protected]<javascript:> >> . >> To unsubscribe from this group, send email to >> [email protected] <javascript:>. >> For more options, visit this group at >> http://groups.google.com/group/h2-database?hl=en. >> > > -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To view this discussion on the web visit https://groups.google.com/d/msg/h2-database/-/A_mF4AUtvXoJ. 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.
