Re: SQL MERGE Question

[Charles Wilt <charles.wilt@xxxxxxxxx>]

It doesn't make sense to
  MERGE INTO

The same table you are
USING (SELECT * FROM)

In your example code, apparently there's no record with manifest =
93000022.

With no rows in b2, nothing will happen.

I suspect you'll want to build a temporary table of some sort, either an
actual table or use a table value constructor to build an in-memory table
on the fly.

WITH X(foo, bar, baz) AS (
VALUES (0, 1, 2), (3, 4, 5), (6, 7, 8)
) SELECT * FROM X;

or

SELECT * FROM TABLE (
VALUES (0, 1, 2), (3, 4, 5), (6, 7, 8)
) X(foo, bar, baz);


So your merge will look more like

MERGE INTO boxdetwk AS b1
             USING (SELECT *
                            from table ( values (93000022,1)) X
(manifest,cnt)
             ) AS b2
             ON (b1.manifest = b2.manifest)
             WHEN MATCHED THEN
             UPDATE SET b1.cnt = b1.cnt + 9999
             WHEN NOT MATCHED THEN
             INSERT (manifest, cnt)
             VALUES (manifest, cnt)

Charles

On Thu, Oct 26, 2017 at 11:14 AM, Art Tostaine, Jr. <atostaine@xxxxxxxxx>
wrote:

> I'm trying to insert or update a record in a table.  I found the MERGE
> statement while googling.
>
> The table has manifest# and cnt (qty) and some other fields that don't
> matter.  Here is my statement.  Once I get this to work, I'll replace the
> values and the where with variables.
>
> MERGE INTO boxdetwk AS b1
>              USING (SELECT * from boxdetwk
>                where manifest = 93000022
>              ) AS b2
>              ON (b1.manifest = b2.manifest)
>              WHEN MATCHED THEN
>              UPDATE SET b1.cnt = b1.cnt + 9999
>              WHEN NOT MATCHED THEN
>              INSERT (manifest,cnt)
>              VALUES (93000022, 1)
>
> When I run this in interactive SQL, I get the below message Row not found
> for MERGE.  What am I doing wrong?  Thank you.
>
> Message ID . . . . . . :   SQL0100       Severity . . . . . . . :   00
>
> Message type . . . . . :   Information
>
>
>
> Message . . . . :   Row not found for MERGE.
>
> Cause . . . . . :   One of the following conditions has occurred:
>
>     -- If this is a FETCH statement, no more rows satisfy the selection
> values
>   (end of file).  The name of the cursor is MERGE and the result set
>
>   identifier is 0. If the result set identifier is non-zero, the result
> table
>   for this cursor was being accessed as a stored procedure result set.
>
>     -- If this is a FETCH statement for a scrollable cursor, a record was
> not
>   found.  If NEXT was specified, end of file was reached.  If PRIOR was
>
>   specified, the beginning of the file was reached.  If RELATIVE was
>
>   specified, either the beginning of file or the end of file was reached,
>
>   depending on the value specified.  If FIRST or LAST was specified, then
> no
>   records satisfy the selection criteria. The name of the cursor is MERGE.
>
>     -- If this is an embedded SELECT statement, no rows satisfy the
> selection
>
>
> --
> Art Tostaine Jr
> Rock Ridge Technology, Inc.
> IBM i on Power Systems & LANSA services
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: https://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at https://archive.midrange.com/midrange-l.
>
> Please contact support@xxxxxxxxxxxx for any subscription related
> questions.
>
> Help support midrange.com by shopping at amazon.com with our affiliate
> link: http://amzn.to/2dEadiD