tags:

views:

49

answers:

2

Sorry I missed and deleted earlier question on accident again.

I have a situation, I am trying to select distinct values from table 1 that are new and store them in table 2. The problem is that table has duplicates on column "name" but it does have a key column "id", but the different ids of course map to the same name.

My idea on the query would be

INSERT INTO TABLE2 
(NAME, UniqueID) 
  SELECT DISTINCT TABLE1.NAME, TABLE1.ID 
  FROM TABLE1 
  LEFT JOIN TABLE2 ON TABLE1.ID=TABLE2.UniqueID 
  WHERE TABLE2.NAME IS NULL 

Need help on getting the query to return my desired results, right now it still produces duplicates in table2 (on name column), which I don't want. I would want it to only append new records even if I run the query multiple times. For example if two new records were added into table1 but one has the name already in table 2, then the query would only add 1 new record to table2

just a note: I am using ms access, so it has strict syntax on single queries

EDIT:

Folliwing input I had came with this query

INSERT INTO TABLE2 
(NAME, UniqueID) 
  SELECT TABLE1.NAME, Min(TABLE1.ID) 
  FROM TABLE1 
  LEFT JOIN TABLE2 ON TABLE1.NAME=TABLE2.NAME 
  WHERE TABLE2.UniqueID IS NULL 
  Group By TABLE1.NAME;

but these actually had to be separated to two separate wueries in access to run without a reserver error flag but now I ran into additional problem. When I run the two separate queries, it works fine the first time, but when I run it twice trying to test to see if any new records have been added to table 1, it then appends 1 record when no new records are in table 1, so it appends a blank name value and a duplicate unique id, and continually does that same process everytime I run it.

+1  A: 
INSERT INTO TABLE2 (UniqueID, NAME)  
SELECT min(t1.ID) as UniqueID, t1.NAME  
FROM TABLE1 t1 
LEFT JOIN TABLE2 t2 ON t1.ID=t2.UniqueID  
WHERE t2.NAME IS NULL  
group by t1.NAME  
RedFilter
nice.. you got me by 21 seconds, almost identical query.
Fosco
this does not work, that is the exact query I have except that it uses the min function on the id field
Jake
@Jake: It is not the same, it is using `GROUP BY`.
RedFilter
@RedFilter- when using the min function with group by in access Im getting a reserved error that has no message for it "reserved error -3087" when I try to run the query
Jake
@Jake: According to this link http://support.microsoft.com/kb/286335 NAME is a reserved word in Access. Can you try again with different column names?
RedFilter
@RedFilter- its not that because I am not actually using name as the column name, its just an alias. once I separated my query from one to two with the insert and select it worked perfectly fine. Not sure if its a problem between Min being in the insert query
Jake
+1  A: 

Since you're pulling both Name and ID, the distinct keyword will only pull distinct combinations of those. Two records with the same Name and different ID's is still valid.

In the case of two Names with different ID's, which would you like to be inserted?...

insert into table2 (Name, UniqueID)
select t1.Name, MIN(t1.ID)
from table1 t1
left join table2 t2 on t1.ID = t2.UniqueID
where t2.Name is null
group by t1.Name

in response to comments, I realize the Name field is what should be joined on, to prevent dupes that already exist.

insert into table2 (Name, UniqueID)
select t1.Name, MIN(t1.ID)
from table1 t1
left join table2 t2 on t1.Name = t2.Name
where t2.UniqueID is null
group by t1.Name
Fosco
this does not work, that is the exact query I have except that it uses the min function on the id field
Jake
See new query addition.. it's the Name that should be joined on, since it seems you already have existing records.
Fosco
It's not the same query then... by using Min() and grouping by the name you will only get 1 record per duplicate name.
Fermin
when using the min function with group by in access Im getting a reserved error that has no message for it "reserved error -3087"
Jake
I think that error means one of your table/column names is a reserved word.
Fosco
I separated the "insert into" query and the select query into two separate queries, and it worked, I don't know why. It seems as if it doesn't like the min function in the same query file as an insert
Jake
ran into additional problem. When I run the two separate queries, it works fine the first time, but when I run it twice trying to test to see if any new records have been added to table 1, it then appends 1 record with no new records are in table 1, so it appends a blank name value and a duplicate unique id
Jake
what do you mean when you run them as 2 separate queries?... how can you run the insert by itself?... (that might enter blanks)..
Fosco
I make the select query by itself with a query name(MRSELECT), then make a insert query pulling data from MRSELECT
Jake