Re: SQL vs Native

[Charles Wilt <charles.wilt@xxxxxxxxx>]

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