Re: SQL MERGE Question -- MIDRANGE-L

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