I'm new to using sql so I'm having difficulties (and worries) in adding a
new column of data to a table I have. Its a very large file (around 5 Gb)
which is why I'm having to use SQL

I have a table with variables ID, IDrec and IDdes and the variables IDrec
and IDdes give a mapping of some other values but the other values are
associated with the ID variable (think of IDrec and IDdes being character
strings and ID being numeric)

(Imagine the transposed)
Table1:
ID: 1,2,3,4,...
IDrec: A,B,C,D...
IDdes: B,C,A,E...

So I've created a table with the final form I need it to be in

dbGetQuery(db, "CREATE TABLE Map
                (ID int, IDrec int, IDrec1 int,
                IDdes int, IDdes1 int)")

And the finished table would look something like:
Map:
ID: 1, 2, 3, 4,...
IDrec: 1, 2, 3, 4,...
IDrec1: A, B, C, D,...
IDdes: 2, 3, 1, 5,....
IDdes1: B, C, A, E,...

So I copy in the first set of values easily:
dbGetQuery(db, "INSERT INTO Map(ID, IDrec, IDrec1, IDdes1)
                SELECT ID, ID, IDrec, IDdes FROM Ntemp")

Giving me a table that looks like:
Map:
ID: 1, 2, 3, 4,...
IDrec: 1, 2, 3, 4,...
IDrec1: A, B, C, D,...
IDdes: NA,NA,NA,NA,...
IDdes1: B, C, A, E,...

Then I create a new table with just the IDdes values I need:
dbGetQuery(db, "Create table temp2 as
                SELECT temp.ID
                FROM Ntemp, temp
                WHERE Ntemp.IDdes1 = temp.IDrec1")

Giving me temp2 (not sure what the variable name is)
V1: 2, 3, 1, 5,...

But when I try to copy in the new data:
dbGetQuery(db, "INSERT INTO Map(IDdes)
                SELECT * FROM temp2")

My map table isn't updated:
Map:
ID: 1, 2, 3, 4,...
IDrec: 1, 2, 3, 4,...
IDrec1: A, B, C, D,...
IDdes: NA,NA,NA,NA,...
IDdes1: B, C, A, E,...

Is there something I'm missing? Or am I just going about inserting the IDdes
variables the wrong way?

Thanks for the help.
Michael

	[[alternative HTML version deleted]]

