help with RSQLite adding a new column
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 <mike409 at 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 at 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.