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