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.
>