The Merge Statement

Introduced in 11.50.xC5 and enhanced in 11.50.xC6 the Merge statement is a nice addition that I really didn't pay much attention to until I learned how it works in preparation for the 11.70 certification exam.

The Merge statement gives you the ability to perform the following SPL in pure SQL:

foreach with hold
   select 
      id, 
      price
   into 
      L_id, 
      L_price
   from 
      products

   update
      warehouse_products
   set
      price = L_price
   where
      id = L_id;

   if dbinfo('sqlca.sqlerrd2') = 0 then
      insert into warehouse_products (id, price) values (L_id, L_price);
   end if;
end foreach;  

Here is the Merge statement accomplishing the same thing with only SQL:

merge into warehouse_products w using products p on
   w.id = p.id
when matched then 
   update set w.price = p.price
when not matched then 
   insert (w.id, w.price) values (p.id, p.price);

Check out the Informix Information Center for more information about deleting on a match, using external tables as the source table and using collection-derived tables as the source table.