Hi Michael, Sorry if I'm being slow, but I've read your post three times and still can't quite work out what you're trying to do (the changing variables names are a bit confusing).
I use RSQLite a lot and might be able to help if you could explain your inputs and desired output in simple terms. (another) Michael On 11 December 2010 05:18, Michael D <mike...@gmail.com> wrote: > 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]] > > ______________________________________________ > R-help@r-project.org mailing list > https://stat.ethz.ch/mailman/listinfo/r-help > PLEASE do read the posting guide http://www.R-project.org/posting-guide.html > and provide commented, minimal, self-contained, reproducible code. > ______________________________________________ R-help@r-project.org mailing list https://stat.ethz.ch/mailman/listinfo/r-help PLEASE do read the posting guide http://www.R-project.org/posting-guide.html and provide commented, minimal, self-contained, reproducible code.
