[R] help with RSQLite adding a new column

[Michael Bedward]

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