Re: SQL vs Native

Why would you be able to use CHAIN with WRITE or UPDATE
Yet need an DELETE and INSERT in SQL?

Brian May mentioned MERGE, allow me to expand and say consider an "upsert"
(aka update/insert) using merge.

Lots of info on the web, the first google results is a stackoverflow
question with a decent example in the answer.
https://stackoverflow.com/questions/330241/does-db2-have-an-insert-or-update-statement

MERGE INTO mytable AS mt USING (
SELECT * FROM TABLE (
VALUES
(123, 'text')
)
) AS vt(id, val) ON (mt.id = vt.id)WHEN MATCHED THEN
UPDATE SET val = vt.valWHEN NOT MATCHED THEN
INSERT (id, val) VALUES (vt.id, vt.val)
;


HTH,

CHarles



On Thu, Aug 18, 2022 at 8:04 AM Greg Wilburn <
gwilburn@xxxxxxxxxxxxxxxxxxxxxxx> wrote:

I'm developing logic to create a "transactional" file/table in an
existing RPG program that creates labels.
I'm trying to capture information about the labels we produce using a
service program procedure and SQL.

The catch is that the users will, on occasion, recreate/reprint a set of
labels. So I need to ensure I'm not creating duplicate transactions.

With native access, I would simply CHAIN then UPDATE or WRITE based on
%FOUND.
With SQL, I would have to do a DELETE then INSERT for each record.
Just wondering if there's an alternative to the SQL method?

I'm trying to go 100% SQL for file access.

Thanks,
Greg
[Logo]<https://www.totalbizfulfillment.com/> Greg Wilburn
Director of IT
301.895.3792 ext. 1231
301.895.3895 direct
gwilburn@xxxxxxxxxxxxxxxxxxxxxxx<mailto:gwilburn@xxxxxxxxxxxxxxxxxxxxxxx>
1 Corporate Dr
Grantsville, MD 21536
www.totalbizfulfillment.com<http://www.totalbizfulfillment.com>
--
This is the RPG programming on IBM i (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/rpg400-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: https://amazon.midrange.com