ibmi-brunch-learn

Announcement

Collapse
No announcement yet.

SQL Update

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

  • SQL Update

    Hi All,

    I have 100 rows in my table. I want to update first 10 row as Row1 and next 10 row as Row2 ... final 10 row as Row10. Is it possible to achieve this using a single update statement?
    Regards,
    Vinothkumar S.

  • #2
    It can be done, using the MERGE statement. MERGE lets you update through a join. We will join your table with a dummy table, that is your table plus row numbering, and use that row numbering to determine the correct value of "row#" to update

    The table in my example is called rdrtst1
    It has fields:
    field1, field2 - these represent the order in which you want to sort your table for the desired row numbering
    rowid - this is the field that needs to be set to 'row1', 'row2', etc

    First, we need to add row and relative record numbering to your table

    Code:
      select rrn(s1) as rr,
             row_number() over(order by field1,field2) as rnum, 
             s1.*
        from rdrtst1 s1
    First we use "rrn()" to return the relative record number of each row, as field name rr
    Then we use "row_number() over()" to add a row number based on whatever order you need (I used field1,field2)
    Then we use s1.* to return the rest of thie fields

    Now, if we wrap this in table(), we can treat this query as an independent table that we can join to

    Code:
      table (
        select rrn(s1) as rr,
               row_number() over(order by field1,field2) as rnum, 
               s1.*
          from rdrtst1 s1) as fileA
    This function returns a table named fileA that is the result of the query. This table can then be used as part of a larger SQL statement


    Now, we look at the MERGE statement. The purpose of merge is to use a single statement to check for a matching record, update it if found, insert if not found:
    Join fileA and FileB
    For each fileA record
    * if a matching fileB record was found, perform an update on fileB using fileA field values
    * if a matching fileB record was not found, perform an insert into fileB using fileB field values


    For our purposes fileB is the table you want to update, and fileA is our query returning the table + row numbering

    Code:
    merge into rdrtst1 as fileB
    
    using table (
            select rrn(s1) as rr, 
                   row_number() over(order by field1,field2) as rnum, s1.*
             from rdrtst1 s1) as fileA
       on rrn(fileB) = fileA.rr
    
    when matched then update
         set rowid = 'row'||char(((fileA.rnum-1)/10)+1)
    We are joining the table to the query on the relative record number (on rrn(fileB) = fileA.rr) to ensure that each row in ther table is matched with the same row in the query. You could also join on the primary key if your table has one. This means there will always be a match, so we are only using the update part of MERGE, not the insert part.

    So when matched, we update the rowid field of the table to the string 'row' plus the desired row number.
    We use a simple bit of maths "(((fileA.rnum)/10)+1)" to transform the row number to what you want: 1 for the first 10 rows, 2 for the next 10, etc.

    And that should do it.

    One caveat - MERGE support is a fairly recent addition, I'm not sure what OS versions support it

    Comment

    Working...
    X