MERGE enhancement: optional DELETE flag

[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

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

[Lukas Eder]

Hello,

Have you had time to think about this idea?

Cheers

Lukas

[Ryan How]

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? :)

--
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 h2-da...@googlegroups.com.
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.

[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

[Lukas Eder]

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

[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

To view this discussion on the web visit https://groups.google.com/d/msg/h2-database/-/AcrHsHDcKAkJ.

[Ryan How]

On 3/09/2012 3:29 AM, Thomas Mueller wrote:
> (as part of the new multi-version storage backend).
>

This sounds exciting :)

[Lukas Eder]

>  I'm working on this (as part of the new multi-version storage backend). 

Great, thanks Thomas!