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]> 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]. > 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. > -- 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.
