Merge statement from H2 database is throwing a "Column count does not match" error-CodePudding

I'm trying to replace Postgres's "on conflict (..) do update.." with a merge statement on an H2 database, I'm however facing an issue I'm unable to figure out.

create table shop (
  name varchar(40) NOT NULL,
  block_nbr bigint DEFAULT 0,
  is_open boolean DEFAULT false,
  PRIMARY KEY (name)
);

Then I try to execute the following merge statement:

MERGE INTO shop USING VALUES('shopname', 3) S(name, block_nbr)
    ON shop.name = S.name
    WHEN MATCHED THEN UPDATE SET name=S.name, block_nbr=S.block_nbr
    WHEN NOT MATCHED THEN INSERT VALUES (S.name, S.block_nbr);

The above query doesn't work, it outputs a "Column count does not match" error. The column names and value count are the same, this error usually happens in insert statements when the number of columns name is not the same as the values given.

I tried to modify the above query by providing the extra value missing but without specifying its corresponding column and it worked but I don't want to insert that value.

MERGE INTO shop USING VALUES('shopname', 3) S(name, block_nbr)
    ON shop.name = S.name
    WHEN MATCHED THEN UPDATE SET name=S.name, block_nbr=S.block_nbr
    WHEN NOT MATCHED THEN INSERT VALUES (S.name, S.block_nbr, true);

CodePudding user response：

It's the INSERT part that fails because the table has 3 columns, but you are only providing 2 values.

MERGE INTO shop 
USING VALUES ('shopname', 3) s(name, block_nbr)
 ON shop.name = s.name
    WHEN MATCHED THEN UPDATE SET name=S.name, bloc_nbr=S.bloc_nbr
    WHEN NOT MATCHED THEN INSERT (name, block_nbr) VALUES (S.name, S.block_nbr)
;