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.
