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.
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.
ReplyDeleteThe 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)"