ibmi-brunch-learn

Announcement

Collapse
No announcement yet.

Update using joins

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

  • Update using joins

    I have to update some date fields in a file where certain conditions are met in other files. I haven't used SQL for updates much but found a sample that I adapted for my situation:

    update a
    set a.lmdcnt = 082014, a.lmdtlc = '2014-08-20'
    from mylib/fklocmst a
    join comastf b
    on a.lmco = b.cmcomp
    join fkitmstr c
    on a.lmco = c.imco and a.lmpn = c.impn
    where a.lmpn <> ' ' and a.lmdtlc = '0001-01-01' and
    b.cmmast = 510 and b.cmsts = ' ' and c.imabc in ('D', 'E')

    While this should work if the example I found is valid, I get "Keyword FROM not expected". I was trying to avoid using subselects in my where clause but will if I have to. Recommendations/examples?

  • #2
    Correct syntax for update is update table set column = value where column = value. Db2 doesn't support joins in an update statement so you will have to use subselects in your where clause.

    Comment


    • #3
      Standard SQL UPDATE statements also don't have FROM clauses, except in subselects. There are a few non-standard implementations that do allow FROM/JOIN, but the potentially incorrect results that can come out of them are serious trouble for important business databases.
      Tom

      There are only two hard things in Computer Science: cache invalidation, naming things and off-by-one errors.

      Why is it that all of the instruments seeking intelligent life in the universe are pointed away from Earth?

      Comment


      • #4
        Using a little trickery and the MERGE statement, you can perform an update using multiple files.

        Code:
        merge into fklocmst as a
        using (select a.cmcomp, b.impn, cast(082014 as integer) as new_lmdcnt, cast('2014-08-20' as char(10)) as new_lmdtlc
               from comastf a
               join fkitmstr b
               on a.cmcomp = b.imco 
               where b.impn <> ' ' and a.cmmast = 510 and a.cmsts = ' ' and a.imabc in ('D', 'E')) as b
        on (a.lmco = b.cmcomp and a.lmpn = b.impn and a.lmdtlc = '0001-01-01')
        when matched then
        update set a.lmdcnt = b.new_lmdcnt, a.lmdtlc = b.new_lmdtlc
        This should work, but make a backup just in case.

        Jim

        Comment


        • #5
          MERGE is great for updating from one table to another, but this update sets two columns to literal values. jj_dahlheimer has the right idea -- use subselects in the where clause.

          Comment


          • #6
            Hi Ted,

            In my example. the literal values were added as derived columns to the Full-Select statement for use during the update statement.

            NEW_LMDCNT = 082014
            NEW_LMDTLC = '2014-08-20'

            But now that you mentioned it, that is unnecessary as well. Just use the literal values in the update statement.

            Code:
            merge into fklocmst as a
            using (select a.cmcomp, b.impn
                  from comastf a
                  join fkitmstr b
                  on a.cmcomp = b.imco
                   where b.impn <> ' ' and a.cmmast = 510 and a.cmsts = ' ' and a.imabc in ('D', 'E')) as b
            on (a.lmco = b.cmcomp and a.lmpn = b.impn and a.lmdtlc = '0001-01-01')
            when matched then
            update set a.lmdcnt = 082014, a.lmdtlc = '2014-08-20'
            Jim

            Comment


            • #7
              Originally posted by Jim_IT View Post
              Hi Ted,

              In my example. the literal values were added as derived columns to the Full-Select statement for use during the update statement.

              NEW_LMDCNT = 082014
              NEW_LMDTLC = '2014-08-20'

              But now that you mentioned it, that is unnecessary as well. Just use the literal values in the update statement.

              Code:
              merge into fklocmst as a
              using (select a.cmcomp, b.impn
              from comastf a
              join fkitmstr b
              on a.cmcomp = b.imco
              where b.impn <> ' ' and a.cmmast = 510 and a.cmsts = ' ' and a.imabc in ('D', 'E')) as b
              on (a.lmco = b.cmcomp and a.lmpn = b.impn and a.lmdtlc = '0001-01-01')
              when matched then
              update set a.lmdcnt = 082014, a.lmdtlc = '2014-08-20'
              Jim
              Is it OK to use 'a' to alias both "fklocmst as a" and "from comastf a" ?

              It's confusing to me, looking at my first MERGE statement.

              I need to do a very similar thing, and tried the same type of UPDATE the OP tried, since it works in MS SQL
              Last edited by MFisher; June 29, 2020, 03:45 PM.

              Comment


              • #8
                Deleted.
                Last edited by MFisher; June 30, 2020, 07:01 PM.

                Comment


                • #9
                  Some platforms allow the use of joins in UPDATE statements, but DB2/IBM i does not. We have to use the MERGE statement or subselects instead.

                  Code:
                  merge into file1 a
                  using file2 b
                     on a.key1 = b.key1
                  when matched then update
                  set a.field2 = b.field3;
                  
                  update file1 a
                  set field2 = (select field3 from file2 b
                                 where a.key1 = b.key1)
                  where exists (select 1 from file2 b
                                 where a.key1 = b.key1);

                  Comment


                  • #10
                    Any idea what's wrong with my syntax ? It works without the
                    WHERE F1.SFSITM = '00037'.
                    I tried the WHERE after the SET also


                    Code:
                    MERGE INTO My_File F1
                    Using  Other_File  S1 ON S1.SASITM = F1.SFSITM and S1.SASEQC = '2' and F1.SFFLYR = S1.SAFLYR [LEFT][COLOR=#000000][FONT=aliceregular][SIZE=13px]WHERE F1.SFSITM = '00037'[/SIZE][/FONT][/COLOR][/LEFT]
                     WHEN MATCHED then UPDATE
                    SET F1.FSV201 = S1.SACS01
                    SQL State: 42601
                    Vendor Code: -199
                    Message: [SQL0199] Keyword WHERE not expected.
                    Last edited by MFisher; June 30, 2020, 10:34 AM.

                    Comment


                    • #11
                      Deleted text
                      Last edited by MFisher; June 30, 2020, 06:55 PM.

                      Comment


                      • #12
                        This is SOOO frustrating. A simple UPDATE in MS SQL, but I just can't get it to work in IBM SQL.

                        UPDATE F1
                        SET F1.Value = (F2.Value_F / S1.Value_S)
                        FROM TABLE_F F1
                        JOIN TABLE_S S1 on S1.SASITM = F1.SFSITM and S1.SASEQC = '1'
                        JOIN Table_F2 F2 on F2.key = F1.Key
                        WHERE F1.SFPERD = '01' AND F1.SFSEQC = '1'
                        and F1.SFSITM = '00037'
                        Last edited by MFisher; June 30, 2020, 07:01 PM.

                        Comment


                        • #13
                          You can't use WHERE in a MERGE statement.
                          Either your condition must be part of the ON clause:

                          Code:
                          MERGE INTO My_File F1
                          Using Other_File  S1
                             ON S1.SASITM = F1.SFSITM and S1.SASEQC = '2' and F1.SFFLYR = S1.SAFLYR
                            and F1.SFSITM = '00037'
                           WHEN MATCHED then UPDATE
                            SET F1.FSV201 = S1.SACS01
                          or part of the WHEN clause (ON is better, this is for when you might want to updater different things in different circumstances)
                          Code:
                          MERGE INTO My_File F1
                          Using Other_File  S1
                             ON S1.SASITM = F1.SFSITM and S1.SASEQC = '2' and F1.SFFLYR = S1.SAFLYR
                           WHEN MATCHED and F1.SFSITM = '00037' then UPDATE
                            SET F1.FSV201 = S1.SACS01
                          As to your most recent post - a MERGE only supports a single join, so the second table will instead need to be a subselect. I think this will work:
                          Code:
                          MERGE INTO TABLE_F fUpd
                          using (select f1.key, F2.Value_F, S1.Value_S
                                   FROM TABLE_F F1
                                   JOIN TABLE_S S1 on S1.SASITM = F1.SFSITM and S1.SASEQC = '1'
                                   JOIN Table_F2 F2 on F2.key = F1.Key
                                  WHERE F1.SFPERD = '01' AND F1.SFSEQC = '1'
                                    and F1.SFSITM = '00037') fRef
                             on fUpd.jkey = fRef.key
                          when matched then update
                           set fUpd.Value = fRef.Value_F / fRef.Value_S;
                          The subselect is a select-only version of your UPDATE statement. It selects TABLE_F's unique key and the required update values. Then you can just join the results of that with TABLE_F again for the merge join

                          I don't know why IBM i doesn't support UPDATE with join, maybe someone else can tell you that

                          Comment


                          • #14
                            Which fields need to be part of the SELECT ? Those used in the UPDATE section ?

                            Code:
                             [LEFT][COLOR=#333333][FONT=Courier]using (select f1.key, F2.Value_F, S1.Value_S[/FONT][/COLOR][/LEFT]

                            Comment


                            • #15
                              Any fields that need to be used outside the subselect. That means those needed for UPDATE (set fUpd.Value = fRef.Value_F / fRef.Value_S), and those needed for ON (on fUpd.key = fRef.key)

                              Comment

                              Working...
                              X