Comparing two postgres tables and updating one with differences.

Posted on 2008-01-28
Medium Priority
Last Modified: 2013-12-12
I have two tables in a postgres database
  Table 1
  Table 2
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);
Question by:MarkApplegate
LVL 48

Accepted Solution

hernst42 earned 2000 total points
ID: 20760882
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 Comment

ID: 20761070
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.

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Build an array called $myWeek which will hold the array elements Today, Yesterday and then builds up the rest of the week by the name of the day going back 1 week.   (CODE) (CODE) Then you just need to pass your date to the function. If i…
Many old projects have bad code, but the budget doesn't exist to rewrite the codebase. You can update this code to be safer by introducing contemporary input validation, sanitation, and safer database queries.
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …
Suggested Courses

601 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question