MySQL Append Query question

[DougieC]

Hello all. First off, Im not a programmer so I am some what stumbling through this. 8 years of social science education never trained me to handle real world problems, such as figuring out how to develop a Data Base for the military.

So a summary of my problem. I am using MySQL, on the suggestion of the higher ups, to combine multi-yeared data into one master table. My logic works like this.

Populate a Master Table with unique individuals from multiple years of data. I start by importing the first years worth of records into the master table. Now, I am running a query to pull the second years records which are not already in the master table (Heres the problem) and adding the new records to the masterfile. Continue with all years infinitely until funding fails.

So the problem, When I run a query to pull all records in the second year that are not in the masterfile, I get a large duplication. In my understanding, the query is taking one record from the second year and matching it to the master file, okay, they match now it drops it. The query now still matching the same record checks it against all other records in the master file and sees that they do not match, this causes the query to pull (number of total records - 1) worth of new records. This is bad, is there some sort of Append Unique code out there? Or a sorting script that will allow me to drop duplicated records?

Any help is much appreciated.

[Motoma]

How is uniqueness determined in your system?

[DougieC]

In this table, each record (or individual) has a SSN as a unique identifier.

[Motoma]

I am using an encrypted ssn as an unique identifier.

INSERT INTO MasterTable VALUES (ssn, field1, field2, field3)
(SELECT ssn, field1, field2, field3 FROM Table2 WHERE ssn NOT IN (SELECT ssn FROM MasterTable))
 

[DougieC]

I appreciate the help. This was the script that I finally used to solve the problem.

insert into `mastertable`
select Table2.*
from Mastertable right join Table2 on mastertable.ssn = table2.SSN
where (((if(mastertable.ssn=Table2.ssn, 0, 1))=1))

[Motoma]

I am glad you found your solution. Thank you for posting it for others to learn from.