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]<javascript:> > > 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]<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 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/-/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.
