469,920 Members | 2,424 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,920 developers. It's quick & easy.

MySQL Append Query question

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.
Jun 12 '07 #1
5 7289
Motoma
3,237 Expert 2GB
How is uniqueness determined in your system?
Jun 13 '07 #2
In this table, each record (or individual) has a SSN as a unique identifier.
Jun 14 '07 #3
Motoma
3,237 Expert 2GB
I am using an encrypted ssn as an unique identifier.
Expand|Select|Wrap|Line Numbers
  1. INSERT INTO MasterTable VALUES (ssn, field1, field2, field3)
  2. (SELECT ssn, field1, field2, field3 FROM Table2 WHERE ssn NOT IN (SELECT ssn FROM MasterTable))
  3.  
Jun 14 '07 #4
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))
Jun 20 '07 #5
Motoma
3,237 Expert 2GB
I am glad you found your solution. Thank you for posting it for others to learn from.
Jun 20 '07 #6

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

11 posts views Thread by Mike MacSween | last post: by
2 posts views Thread by JMCN | last post: by
reply views Thread by Daniel Crespo | last post: by
1 post views Thread by bruce | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.