Re: SQL MERGE -- MIDRANGE-L

We use and have used merge with parameter values for a while now, currently
on 7.4.

What version (and TR level)?

What language do you have the statement embedded in? I assume RPG IV.

I don't really see any issues with your syntax, looks just like one of ours.

I will note that we tend to use unique column names for the source data so
that we don't have to qualify them.

MERGE INTO arp001 USING (VALUES (:value1, :value2))
AS newdata (cust_num, cust_name)
ON cmcust = cust_num
WHEN NOT MATCHED THEN *INSERT* (cmcust, cmname) VALUES
(cust_num, cust_name)
WHEN MATCHED THEN UPDATE SET cmname = cust_name;

Lastly, I'll note that for a data conversion project... a one row at a time
UPSERT is going to be very, very slow.

I'd dump all the data into a table, come up with a SELECT statement that
gives me a unique set of values...then use that SELECT in the MERGE to
update everything.

Charles

On Sat, Jul 29, 2023 at 11:21 PM x y <xy6581@xxxxxxxxx> wrote:

In a data conversion exercise, I'm getting dirty data and that's causing
lots of duplicate key exceptions (which appear to have a big performance
hit). Using MERGE came up as a potential solution and I RTFM (meaning I
Googled).

Following an example in the z/OS DB2 manual, this works:
MERGE INTO arp001 USING (VALUES ('X1', 'X1 DESC'))
AS newdata (cmcust, cmname)
ON arp001.cmcust = newdata.cmcust
WHEN NOT MATCHED THEN *INSERT* (cmcust, cmname) VALUES
(newdata.cmcust, newdata.cmname)
WHEN MATCHED THEN UPDATE SET cmname = newdata.cmname;

But this doesn't. The change is in using program variables instead of
literals.
MERGE INTO arp001 USING (VALUES (:value1, :value2))
AS newdata (cmcust, cmname)
ON arp001.cmcust = newdata.cmcust
WHEN NOT MATCHED THEN *INSERT* (cmcust, cmname) VALUES
(newdata.cmcust, newdata.cmname)
WHEN MATCHED THEN UPDATE SET cmname = newdata.cmname;

Message: [SQL0584] NULL, UNKNOWN, or parameter marker in VALUES not
allowed. Cause . . . . . : Each column in a VALUES clause must have a
data type. All rows for a column cannot contain NULL, UNKNOWN, a parameter
marker, or the RAISE_ERROR scalar function. Recovery . . . : Ensure
every column has at least one value with a defined type. A CAST
specification can be used to assign a type. Try the request again.

Table ARP001 has more than two columns, all type 3 (not null with default).

ACS's "Run SQL Scripts" option provides this example:
MERGE INTO t1 USING
(SELECT id, c2 FROM t2) x ON
t1.id = x.id
WHEN NOT MATCHED THEN *INSERT* VALUES (id, c2)
WHEN MATCHED THEN UPDATE SET c2 = x.c2;

It appears that either program variables aren't allowed or I have to
specify every column name in WHEN NOT MATCHED.

Suggestions/solutions will be appreciated--thank you!
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.