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/**B28359_01/server.111/b28286/** >> statements_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. > 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. > -- 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.
