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 [email protected]. To view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/13b39cfc-0430-4201-a3a7-6ae9ec5a4ec6n%40googlegroups.com.
