MERGE Statement is not working in Informix v10

Question

I tried to use the merge statement to Insert/Update in Informix v10.0 but it throws a Syntax Error:

create table source(id int, account int, age int);
create table target (id int, account int, age int);

insert into source values(1, 1200, 25);
insert into source values(2, 1300, 28);
insert into source values(3, 1400, 45);

merge into target t 
using source s on t.id = s.id
when matched then
update
set t.id = s.id, t.account = s.account, t.age = s.age
when not matched then
insert (t.id, t.account, t.age)
values (s.id, s.account, s.age);

select * from target;

Could you please help?

Answer 1

The MERGE statement was not available in Informix 10.00. It was added in 11.50 — see the MERGE statement in the SQL syntax manual. The new features page suggests it was added in 11.50.xC6, part of the way through the maintenance release cycle.

Note that version 10.00 and all the 11.x versions (11.10, 11.50, 11.70) are unsupported. Version 11.70 went out of support on 2020-10-01.