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.
