ibmi-brunch-learn

Announcement

Collapse
No announcement yet.

Optimaze Delete in SQLRPGLE

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

  • Optimaze Delete in SQLRPGLE

    Delete FROM
    Filea left join FileB on
    Filea.field1 = Fileb.file1 and
    Filea.field1 = Fileb.file2 and
    Filea.field1 = Fileb.file3
    where
    Fileb.file1 is null

    Any way how to optimaze this statment?
    I want to delete the record that exist in FileA that dosen't existe in FileB

  • #2
    If I understand your questions I made something like this:


    Code:
    Declare global temporary table FileA 
    (Field1    Char(10) not null with default '');
    
    Declare global temporary table FileB
    (File1    Char(10) not null with default '', 
     File2    Char(10) not null with default '',
     File3    Char(10) not null with default '');
    
    
    
    Insert into Filea Values('TEST1');
    Insert into Filea Values('TEST2');
    Insert into Filea Values('TEST3');
    Insert into Filea Values('TEST4');
    Insert into Filea Values('TEST5');
    
    Insert into FileB (File1) Values('TEST1');
    Insert into FileB (File2) Values('TEST2');
    Insert into FileB (File3) Values('TEST3');
    
    Select *From FileA;
    Select *From Fileb;
    
    
    Delete FileA
    where Field1 not in 
    (select File1 from fileB)
    and Field1 not in 
    (select File2 from fileB)
    and Field1 not in 
    (select File3 from fileB);
    Probably there are best solutions.
    Try it before doing

    Bye

    Comment


    • #3
      It's got to be somethink like this

      Delete FileA
      where Field1, Field2, Field3 not in
      (select Field1, Field2, Fild3 from fileB)
      FileA FileB
      Field1 Field2 Field3 Field4 Field1 Field2 Field3 Field4
      1 2 3 4 1 2 3 4
      1 2 5 6 1 2 3 5
      1 2 9 9 1 2 3 6
      1 2 5 6
      In this example i wanto to eliminate only the 3 record of the File (1 2 9 9) becouse it dosen't have a match of the 3 first field (Field1, Field2 e Field3) on FileB.

      Comment


      • #4
        If is so you can simply modifies your sql:

        Code:
        Delete FileA 
        where (Field1, Field2, Field3) not in 
        (select Field1, Field2, Fild3 from fileB)
        Add the parenthesis and it has to work.
        Bye

        Comment


        • #5
          Originally posted by Thunder View Post
          Delete FROM
          Filea left join FileB on
          Filea.field1 = Fileb.file1 and
          Filea.field1 = Fileb.file2 and
          Filea.field1 = Fileb.file3
          where
          Fileb.file1 is null

          Any way how to optimaze this statment?
          I want to delete the record that exist in FileA that dosen't existe in FileB
          delete from filea a where not exists (select * from fileb b where a.field1=b.field1 and a.field2=b.field2 and a.field1=b.field3)

          I don't think your version of delete will work at all. Though I'm guessing that you really want or rather than and (looking to see if field1 is equal to ANY of the three fields change the 'and' in the subselect to 'or') - if you want field1 to have same value of all three fields keep the 'and'.

          Comment


          • #6
            Originally posted by Rocky View Post

            delete from filea a where not exists (select * from fileb b where a.field1=b.field1 and a.field2=b.field2 and a.field1=b.field3)

            I don't think your version of delete will work at all. Though I'm guessing that you really want or rather than and (looking to see if field1 is equal to ANY of the three fields change the 'and' in the subselect to 'or') - if you want field1 to have same value of all three fields keep the 'and'.
            Sorry

            Delete FROM
            Filea left join FileB on
            Filea.field1 = Fileb.file1 and
            Filea.field2 = Fileb.file2 and
            Filea.field3 = Fileb.file3
            where
            Fileb.file1 is null

            Comment


            • #7
              Your delete will work at all. You can't join the files as part of the delete. You have to change it to delete from filea where not exists.... or delete from filea where not in (...

              Comment


              • #8
                Ok - the first thing I always do is create a SELECT with the data I want to keep - and if it's anything beyond a simple select I'll create a view. In your example given above:

                Code:
                SELECT
                  A.*
                FROM
                   testfile.filea A
                JOIN 
                   testfile.fileb B
                on
                  A.field1 = B.field1 and A.field2=B.field2 and A.field3=B.field3
                This gives the "valid" data that you wish to keep. Because the next step the above would make it complicated I make a view...

                Code:
                CREATE VIEW QTEMP.VALIDVALUES AS
                SELECT
                  A.*
                FROM
                   testfile.filea A
                JOIN 
                   testfile.fileb B
                on
                  A.field1 = B.field1 and A.field2=B.field2 and A.field3=B.field3
                Now I have a view containing only the data I wish to keep - now I can delete the rest - so I do a test using a select:

                Code:
                select *                                                            
                from testfile.filea a                                               
                where 
                  not exists (select * from qtemp.validvalues b                 
                   where 
                      a.field1 = b.field1 and 
                      a.field2 = b.field2 and 
                      a.field3=b.field3);
                This returns the row(s) that you do NOT want - so change the SELECT * to DELETE

                Code:
                DELETE
                from testfile.filea a                                               
                where 
                  not exists (select * from qtemp.validvalues b                 
                   where 
                      a.field1 = b.field1 and 
                      a.field2 = b.field2 and 
                      a.field3=b.field3);
                And that way you know what you're deleting it - the last one deletes the records that showed up in the SELECT just prior.

                Comment


                • #9
                  Back to the original question - I'd create that view and use the DELETE statement in the program.

                  Comment

                  Working...
                  X