Hi, Vipul by mistake send the mail below to me instead of to the group. See my reply below.
On Thu, Jan 21, 2010 at 5:32 PM, vips <[email protected]> wrote: > Hi, > > I am writing on this thread since I am trying to write something > similar to what Brian mentioned on Dec 5. > > Using the same example, > Lets say there are 2 tables - Totals and Increments. > Totals always stored the cumulative data and Increments is > continuously uploaded with incremental data. > After the data from Increments is merged into Totals, data from the > Increments table is removed/truncated. > > Lets say Totals has a column called total which is sum of all the > increments till date for a particular id. > Hence, while merging the data the already existing values in the total > column of the Totals table need to be summed up / added to the new > values obtained from the Increments table's inc column. > > Hence, what is actually required is something like - > update Totals set total = total + (select inc from Increments where > Increments.id = Totals.id) > > If this update does not succeed or returns 0 rows updated, then > Insert into Totals (id, total) (select id, inc from Increments) > > Notice that the update operation is using some existing values of the > Totals table to do some mathematical operation with the newer values > received from Increments table. > > Can the above be done using the MERGE statement of H2? > I am not sure, since the MERGE statement does not provide a way to > provide an expression using the existing values of the merged table. > > Could something like the following work ? - > MERGE into totals (id, total) key (id) values (select id, totals.total > + Increments.inc from Increments) > > Such cases would come in more practical use. > Mostly updated columns will need to be added, subtracted, multiplied, > averaged, etc while doing an update. > > As Thomas rightly pointed out the current MERGE command is more like a > MySQL REPLACE command, > rather than really a practical way of "Update if-exists else Insert". > > If it is not directly available as of now - can this be made as a > feature request? > > Regards, > Vipul. Hi, > Can the above be done using the MERGE statement of H2? What about: drop all objects; create table totals(id int primary key, total decimal); create table increments(id int, inc decimal); insert into totals values(1, 10); insert into increments values(1, 1), (1, -2), (2, 10), (2, -3); merge into totals(id, total) select i.id, ifnull(t.total, 0) + sum(i.inc) from increments i left outer join totals t on i.id = t.id group by t.id; select * from totals; Regards, Thomas -- 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.
