Hi all. Sorry for the noise. I just found the WHEN MATCHED and WHEN NOT MATCHED clauses. These will solve my problem nicely. Thanks again.
On Friday, 8 July 2022 at 16:18:46 UTC+2 Silvio wrote: > Hi all, > > This question may be a generic SQL one but since I am using H2 and some > special feature may be available I will try my luck here. > > I am trying to perform a sequence of MERGE-statements as part of an > attempt to repopulate database tables from some form of custom > database-logging. > > The tables all contain a column > > _INSERTED DEFAULT CURRENT_TIMESTAMP > > and a column > > _UPDATED ON UPDATE CURRENT_TIMESTAMP > > which works great for INSERT, UPDATE and MERGE statements alike when > performed in actual time. > > My logging contains a timestamp T that tells me when DELETE, INSERT, > UPDATE or MERGE statements have executed. When redoing the statements I > need to replace the CURRENT_TIMESTAMP values with that specific timestamp. > Since I do not think I can temporarily manipulate the value returned by > CURRENT_TIMESTAMP I am doing this the hard way: > > When doing an INSERT I simply include the _INSERTED column and use T as > the value. The same goes for UPDATE statements and the _UPDATED column. > This all works fine. > > But I get stuck with MERGE statements. Since I can not tell up front if > the MERGE will result in an INSERT or and UPDATE I do not know if I need to > supply the _INSERTED+_UPDATED (INSERT case) or just the _UPDATED (UPDATE > case) columns along with their T value. > > Is there any way I can do a MERGE and supply an extra column+value only > for the case that an INSERT will take place? Or is there a way I can tell > H2 to resolve CURRENT_TIMESTAMP as T and simply leave out both columns? > > -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/13b39cfc-0430-4201-a3a7-6ae9ec5a4ec6n%40googlegroups.com.
