Shiny wrote:
> I'm hoping to use SELECT FROM MERGE in one of my stored procedures,
> but have not had any luck running my query. The sample query I'm using
> is
>
> SELECT TAPE_ID
> FROM FINAL TABLE
> (
> MERGE INTO MERGETEST AS mt
> USING
> (
> SELECT '3633' as TAPE_ID FROM sysibm.sysdummy1
> ) AS indata
> ON mt.TAPE_ID = indata.TAPE_ID
> WHEN NOT MATCHED THEN
> INSERT ( TAPE_ID ) VALUES( '3633' )
> ELSE IGNORE
> );
>
>
> We are using DB2 v9.1.0.356 and I have been reading about SELECT from
> MERGE in a few articles. An older post here (http://groups.google.com/
> group/comp.databases.ibm-db2/browse_thread/thread/
> 108e3a0eb3cb79d4/5da632c29830dde0?lnk=gst&q=select+from+merge&rnum= 3#)
> suggests that is not possible, but the post is from Sep. 2006 and I
> wonder if things have changed since. The article that mentions SELECT
> FROM MERGE is http://www.db2portal.com/2007_04_01_archive.html and I
> thought I saw the same in DB2 documentation as well.
>
> Has anybody tried and succeeded with this statement?
SELECT FROM MERGE is supported only on DB2 9 for zOS
(and I'm green with envy!)
Now MERGE in DB2 9 for zOS however only allows the VALUES clause as a
source table.
Basically the SELECT FROM MERGE is geared towards NOT ATOMIC batch merges.

Replace:
> (SELECT '3633' as TAPE_ID FROM sysibm.sysdummy1)
with
(VALUES '3633') AS S(TAPE_ID)

That should do it on zOS.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab