2 Replies Latest reply: Apr 30, 2015 6:48 PM by Scott Samuel RSS

    Where Not Exists In Table Only

    Scott Samuel

      I'm trying load additional rows into a table while avoiding duplication with existing rows. See the demo load script below. The result in Tab 2 is:

      F1 F3

      A Large

      B Small

       

      The Where Not Exists clause is checking the entire field F1. Can it be forced to check only within Tab 2 so the end result is:

      F1 F3

      A Large

      B Small

      C Medium

       

      Ie, a row for C is added but A and B are not duplicated? I have a workaround involving temporarily duplicating F1: is there a better approach?

       

      [Tab 1]:
      load *
      inline
      [F1, F2
      A, Regular
      B, Premium
      C, Occasional]
      ;

      [Tab 2]:
      load *
      inline
      [F1, F3
      A, Large
      B, Small]
      ;

      // Does not load (C, Medium) since C already exists in Field F1
      concatenate([Tab 2])
      load F1
      ,'Medium'
      as F3
      resident [Tab 1]
      where not exists(F1);