Thursday, June 9, 2011

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.

1 comment:

  1. This is a bit kludgy if I'm just dealing with a single row. Any sort of elegant way to use a set of constants in the USING clause instead of a table? I was able to hack it by SELECTing the constants I want to use FROM systables WHERE tabid = 1, but that's an ugly, ugly hack.

    The simple use case is this:

    I have a table that has a bunch of counters in it. Using regular SQL, I want to be able to say, "if a column exists with pkcol = 'TG', increment counter_a by 1; if it doesn't exist, insert it, setting pkcol to 'TG' and counter_a to 1."

    Something like:

    MERGE INTO mytab a
    ON pkcol = "TG"

    WHEN MATCHED
    UPDATE (counter_a) = (counter_a + 1)

    WHEN NOT MATCHED
    INSERT (pkcol, counter_a) VALUES ('TG',1)"

    ReplyDelete