ibmi-brunch-learn

Announcement

Collapse
No announcement yet.

Cloning records to same file with changes

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

  • Cloning records to same file with changes

    Say I have table MyFile, with key column a.
    And all rows where a = 'old', I want to clone with a = 'new', within an SQLRPGLE program.

    The simple way:

    Code:
    exec sql
    insert into MyTable (a, b, c, d)
    select 'new', b, c, d
      from MyTable
     where a = 'old';
    But this is frustrating because it means you have to specify all the columns, so the program needs recompiling to account for new columns.

    I thought of this way:
    Code:
    exec sql
    declare global temporary table MyTable# as (
    select *
      from MyTable
     where a = 'old')
    with data;
    
    exec sql
    update QTEMP/MyTable#
    set a = 'new';
    
    exec sql
    insert into MyTable
    select * from QTEMP/MyTable#;
    
    exec sql drop table QTEMP/MyTable#;
    Which works, but it's a 4 step process that requires copying all the records to another table before copying them to the main table

    Is there a better way?

  • #2
    If you're talking about using this in a program you can read / fetch it into a data structure, change the value and insert the new record using that data structure.

    Code:
    Dcl-DS F5701A  Extname('F5701A') QUALIFIED;  
    End-DS F5701A;
    ...
    Exec SQL Select * into :F5701A 
             from F5701A 
             where $x$pcl=1; 
    
    F5701A.$x$pcl = 99999; 
    
    Exec SQL Insert into F5701A 
             values(:F5701A);
    The above code only works where you are positive you are only selecting one row at a time - which is what your example shows as well.

    Comment


    • #3
      But this is frustrating because it means you have to specify all the columns, so the program needs recompiling to account for new columns.
      Just a footnote. If you use 'SELECT * ...' in your program (which the purists here will say you should always list out the fields) your program will run after adding new fields to a table but the new fields won't be recognized until you recompile - so technically you will need to recompile for new columns, it's just that using the '*' eliminates the need to modify all of the SQL statements to see the new columns.

      Comment


      • #4
        In my experience, the insert method you specify fails with SQLSTT = 42802 (The number of target values is not the same as the number of source values) if I add a column without recompiling (so there are less DS subfields than there are table columns)

        Comment


        • Rocky
          Rocky commented
          Editing a comment
          On an insert that would be a true statement... but if you're using a DS built from an EXTNAME - and using SELECT *... if you're not needing to initialize the new fields it's a matter of recompiling.

          If you're not inserting new records it will continue to run fine w/o recompiling.

      • #5
        Should be possible with a host-structure array:
        Code:
        Insert into YourSchema.YourTable x Rows  Values (:HOSTARRAYDS);
        Birgitta

        Comment

        Working...
        X