Re: SQL vs Native

[Charles Wilt <charles.wilt@xxxxxxxxx>]

Note that the upsert merge can (and should if possible) be done with
multiple rows at once...

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

Charles

On Fri, Aug 19, 2022 at 7:12 AM Greg Wilburn <
gwilburn@xxxxxxxxxxxxxxxxxxxxxxx> wrote:

> Brian/Charles and others... thanks for he suggestions.  I was not aware of
> the MERGE option.
> ...and as for Google searches - that's usually my "go to".  While the
> results very useful they don't always return the "best" way of dong things.
>
> Turns out I was able to tailor the application so that I could deleted
> groups of records/rows based on a common key.
> I still need to create rows one-at-a-time. But SQL is definitely solved
> the problem.
>
>
> -----Original Message-----
> From: RPG400-L <rpg400-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Greg
> Wilburn
> Sent: Thursday, August 18, 2022 9:05 AM
> To: RPG programming on IBM i <rpg400-l@xxxxxxxxxxxxxxxxxx>
> Subject: SQL vs Native
>
> 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
> --
> 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
> --
> 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