Informix - where clause with merge into

Question

I am using Informix Dynamic Server Version 12.10.FC9W1X2.

I need to update a table with fields from a different table. I am attempting to use the

MERGE INTO 

statement, but I am not able to use a WHERE CLAUSE to filter out information from the table that is being updated as it's causing a syntax error. I also tried adding AND to the WHEN MATCHED and it isn't working.

Is there any way to do this?

This is my statement so far,

MERGE INTO table1 as t1
USING table2 as t2
   ON t1.some_no = t2.some  
WHEN MATCHED   
    THEN 
UPDATE SET t1.some_other_no = t2.some_other_no, is_processed = 'Y', resolution = 'AAA'

Answer 1

The additional filters on table1 can be placed in the ON clause. For example:

create table t1(col1 int, col2 int, col3 int);

insert into t1 values(1,1,1);
insert into t1 values(1,2,0);
insert into t1 values(1,3,0);
insert into t1 values(2,1,0);
insert into t1 values(3,1,1);

create table t2 (col1 int, col2 int);

insert into t2 values(1,5);
insert into t2 values(2,5);
insert into t2 values(3,5);


merge into t1
using t2
on
    t1.col1 = t2.col1
AND t1.col3 = 1
when matched then
    update set t1.col2 = t2.col2;

The above results in the following output when selecting from the t1 table

       col1        col2        col3

          1           5           1
          1           2           0
          1           3           0
          2           1           0
          3           5           1