Question about MERGE statement

[Silvio]

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?

[Silvio]

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.