We help IT Professionals succeed at work.

Comparing two postgres tables and updating one with differences.

MarkApplegate
on
Medium Priority
296 Views
Last Modified: 2013-12-12
I have two tables in a postgres database
  Table 1
        newacctnumber
        oldaccountnumber
        date
  Table 2
        newacctnumber
        oldaccountnumber
        done
Table 2 contains all the newacctnumber/oldaccountnumber entrees that exist in table 1.  Some other steps are take and then the done is set to Y.

Table 1 is automatically generated each week, resulting in additional entreens being added.

What I need to do is update table 2 with new entrees that are in Table 1 but not already in Table 2.

This will be done thru an online page (administrators page) with php code.  I have one method but it seems slow to me.

      $query="Select * from table1";
      $db = "poscoid";
      $result = db_query($query, $db);
      
      while ($r=pg_fetch_array($result))
               {
               $newacctnumber = $r["newacctnumber"];
               $oldacctnuber = $r["oldacctnumber"];
   
    $query="Insert into table2(newacctnumber, oldacctnumber) values($newacctnumber,$oldacctnumber) where $newacctnumber not in (select newacctnumber from table2 where done = 'N')";
      
      $db = "poscoid";
      $updateresult = db_query($query, $db);
      
          }
Comment
Watch Question

Top Expert 2007
Commented:
You could try something like (from mysql, but might also work for postgres). Maybe you need to move the result of select newacctnumber from table2 where done = 'N') first to a temp-table:

INSERT INTO table2(newacctnumber, oldacctnumber)  SELECT table1.newacctnumber, table1.oldacctnumber FROM table1 WHERE table1.newacctnumber  not in (select newacctnumber from table2 where done = 'N')

Author

Commented:
Thanks for the brain jog hernst42.  I knew there was a way to avoid the php, just couldn't force it out of the grey matter.

Explore More ContentExplore courses, solutions, and other research materials related to this topic.