ibmi-brunch-learn

Announcement

Collapse
No announcement yet.

Change Select to Update

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Change Select to Update

    What is the best/easiest way to convert a Select SQL statement containing a CTE and join to another file into an UPDATE? The following gives me the accounts I want to target, so now I want to update a field to a different value.

    Code:
    with writeoffs as (select zd$gnr as WOAcct, (zd$ndy * 10000 + zd$ndm * 100 + zd$ndd) as WODate from F58211  where zd$ttp = 'WO')
     Select ZB$GNR as Acct, ZB$STR as AcctStore, WODate
       From F58201 accts join writeoffs on accts.ZB$GNR = WOAcct
      Where accts.zb$ASC = 'C'
    The records selected by this SQL will have their ZB$ASC field updated from 'C' to 'L', so I'm just looking for advice on the best way to convert these kinds of SQL statements from select to update.

    Thanks!

  • #2
    Viking,

    The MERGE statement allows a full-select statement to be used to update a table. CTE's are not allowed though.

    Code:
    Merge F58201 as OUTPUT
    Using
    (Select
    ZB$GNR
    From F58201
    Join F58211
    On ZB$GNR = ZD$GNR
    And ZD$TTP = 'WO'
    Where ZB$ASC = 'C') As INPUT
    On
    OUTPUT.ZB$GNR = INPUT.ZD$GNR
    When Matched Then Update Set ZB$ASC = 'L'
    Jim

    Comment


    • #3
      Jim (or anyone),
      I'm just curious... which is faster, Merge or Update?
      Regards

      Kit
      http://www.ecofitonline.com
      DeskfIT - ChangefIT - XrefIT
      ___________________________________
      There are only 3 kinds of people -
      Those that can count and those that can't.

      Comment


      • #4
        Jim, thank you very much! The MERGE worked and will be useful for me going forward too.

        This is what my statement ended up looking like. The Merge in your example needed an INTO, and I added a DISTINCT because I found some accounts that had multiple WO transactions and therefore caused the input records to have duplicate accounts numbers, which the Merge did not like.

        It ran quickly and accurately, and it's great being able to first see the records that will be affected by just doing the SELECT portion, and then doing the update on those records by wrapping the MERGE around it. I do wonder why the MERGE can't deal with being around a SELECT that contains a CTE, but I will keep that in mind.

        Thanks!

        Code:
        Merge into F58201 as OUTPUT
        Using
        (Select distinct ZB$GNR
        From F58201
        Join F58211
        On ZB$GNR = ZD$GNR
        And ZD$TTP = 'WO'
        Where ZB$ASC = 'C') as INPUT
        On
        OUTPUT.ZB$GNR = INPUT.ZB$GNR
        When Matched Then Update Set ZB$ASC = 'L'

        Comment

        Working...
        X