appending "difference" between tables 1

[admoore]

I have two table with identical structure.

I would like to append table 1 with all records from table two only where a key field does not have a match in table one already.

I was able to create a join query to find the common records between the two

select oldbonus.vin, oldbonus.amount from oldbonus join bonus on oldbonus.vin=bonus.vin;

but haven't been able to figure out how to delete the results of this query from the old table, which would give me the remaining records to append to the new table....

Help with either approach would be appreciated.

TIA,

-A

 

[AdaHacker]

You're probably looking for something like this:

SELECT oldbonus.vin, oldbonus.amount
  FROM oldbonus LEFT JOIN bonus
    ON oldbonus.vin = bonus.vin
 WHERE bonus.vin IS NULL;

The results of the LEFT JOIN will have all the rows from oldbonus. If one of them does not have a corresponding row in bonus, the bonus fields will all be NULL. You can exploit that by adding the WHERE clause to select only those rows. The net result should be all the rows in oldbonus that don't exist in bonus.