Re: [sqlite] how to add a new column quickly
Yes, it is fine. Thanks very much. 2007/5/7, Mohd Radzi Ibrahim <[EMAIL PROTECTED]>: How about this? update tablename set newcolname=(case rowid when 1 then 1 else 2 end); best regards, Radzi - Original Message - From: "Tomash Brechko" <[EMAIL PROTECTED]> To: <sqlite-users@sqlite.org> Sent: Saturday, May 05, 2007 8:09 PM Subject: Re: [sqlite] how to add a new column quickly > On Sat, May 05, 2007 at 19:30:59 +0800, ronggui wong wrote: >> Thanks. But there is no typo, what I want is a general solution. >> >> 2007/5/5, Tomash Brechko <[EMAIL PROTECTED]>: >> >On Sat, May 05, 2007 at 14:01:56 +0800, ronggui wong wrote: >> >> . update tablename set newcolname=1 where ROWID=1 >> >> . update tablename set newcolname=2 where ROWID=2 >> >> . update tablename set newcolname=2 where ROWID=3 > > If there is no correlation between newcolname and other columns that > can be expressed as a formula, but rather you want to set newcolname > to some known Func(ROWID), you may register this function with > sqlite3_create_function() (or its equivalent for your language > bindings), and then do a single statement > > UPDATE tablename SET newcolname = Func(ROWID); > > This will be faster then repeatedly searching for a row with a given > ROWID. > > > -- > Tomash Brechko > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > > - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] how to add a new column quickly
Thanks. But there is no typo, what I want is a general solution. 2007/5/5, Tomash Brechko <[EMAIL PROTECTED]>: On Sat, May 05, 2007 at 14:01:56 +0800, ronggui wong wrote: > . update tablename set newcolname=1 where ROWID=1 > . update tablename set newcolname=2 where ROWID=2 > . update tablename set newcolname=2 where ROWID=3 > . > > My question is: how to add the above task efificiently? Thanks! If there is a typo in the last line, and it should have been 'newcolname=3' (not 2), then your operation is effectively UPDATE tablename SET newcolname = ROWID; -- Tomash Brechko - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] how to add a new column quickly
I would like to add a new column to an existing table. I use .alter table tablename add newcolname to add a new col, and use . update tablename set newcolname=1 where ROWID=1 . update tablename set newcolname=2 where ROWID=2 . update tablename set newcolname=2 where ROWID=3 . My question is: how to add the above task efificiently? Thanks! Ronggui Huang - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] can ignore n lines when .import data file?
2006/1/7, Griggs, Donald <[EMAIL PROTECTED]>: > > Regarding: > > I want to use .import ... but the first line is the variable(fields), so I > want to ignore it. > = > > Possible option #1: > > If it wouldn't violate any database-enforced constraints, why not import the > entire file, then delete the first row via SQL? > DELETE FROM mytable WHERE ROW_ID=1; I have tried and works fine for me. one more thing,is seems ROWID instead of ROW_ID.I use sqlite 3.2.8. > > Possible option #2: > > Are you using windows? If not, and if you weren't using SED in a piped > command (i.e., if you were copying the data via SED to a new file), then > changing to a piped command might lessen the time. > (Under windows, I believe a full file copy to a temporary file occurs with > piped commands, so I would expect no advantage.) > > > Possible option #3: > > You could instead modify the source of the command-line tool, of course. > > > > Donald Griggs > > > Opinions are not necessarily those of Misys Healthcare Systems nor its board > of directors. >
Re: [sqlite] can ignore n lines when .import data file?
2006/1/7, Jay Sprenkle <[EMAIL PROTECTED]>: > Have you tried "head" or "tail"? do you means the head command of the OS,say linux ? Not yet,using "head" or "tail" will be faster than sed? As I see,many CSV files using \t as separator. And SQLite does not use \t,I have to use sed(or other tools) to change Tab to |. so My puzzle is why SQLite not support \t? > On 1/6/06, ronggui wong <[EMAIL PROTECTED]> wrote: > > I want to use .import to import a data file into SQLite.but the first > > line is the variable(fields),so I want to ignore it.delete the first > > row of the data is one solution,But the data file is large ,and try to > > use sed to delete the first row,it takes long time > > a|b|c > > 1|2|3 > > |3|1 >
Re: [sqlite] can ignore n lines when .import data file?
2006/1/7, Griggs, Donald <[EMAIL PROTECTED]>: > > Regarding: > > I want to use .import ... but the first line is the variable(fields), so I > want to ignore it. > = > > Possible option #1: > > If it wouldn't violate any database-enforced constraints, why not import the > entire file, then delete the first row via SQL? > DELETE FROM mytable WHERE ROW_ID=1; > Possible option #2: > > Are you using windows? If not, and if you weren't using SED in a piped > command (i.e., if you were copying the data via SED to a new file), then > changing to a piped command might lessen the time. > (Under windows, I believe a full file copy to a temporary file occurs with > piped commands, so I would expect no advantage.) > I am using windows. > Possible option #3: > > You could instead modify the source of the command-line tool, of course. > > > > Donald Griggs > > > Opinions are not necessarily those of Misys Healthcare Systems nor its board > of directors. >
[sqlite] can ignore n lines when .import data file?
I want to use .import to import a data file into SQLite.but the first line is the variable(fields),so I want to ignore it.delete the first row of the data is one solution,But the data file is large ,and try to use sed to delete the first row,it takes long time a|b|c 1|2|3 |3|1 ...
Re: [sqlite] how can I import CSV file into SQLite quickly
sorry ,maked an mistake.
actually,the data size is 805 vars, 118519 obs.
2006/1/5, ronggui wong <[EMAIL PROTECTED]>:
> Thanks to all give response to help.
> This is my solution using the luanguage I familiar.(http://www.r-project.org).
>
> I use the code to read a 11819x807 csv file and it takes 10 minus.I think is
> not too slow .(My PC:1.7G,512M RAM)
>
> #code begins
> rm(list=ls())
> f<-file("D:\\wvsevs_sb_v4.csv","r")#134M
> i <- 0
> done <- FALSE
> library(RSQLite)
> con<-dbConnect("SQLite","c:\\sqlite\\database.db3")
> tim1<-Sys.time()
>
> while(!done){
> i<-i+1
> tt<-readLines(f,2500)
> if (length(tt)<2500) done <- TRUE
> tt<-textConnection(tt)
> if (i==1) {
> assign("dat",read.table(tt,head=T,sep=",",quote=""));
> # to make the variable names elegent
> nam<-names(dat);
> nam<-gsub("^X.","",nam);
> nam<-tolower(gsub(".$","",nam))
> names(dat)<-nam
> #
> }
> else assign("dat",read.table(tt,head=F,sep=",",quote=""))
> close(tt)
> ifelse(dbExistsTable(con, "wvs"),dbWriteTable(con,"wvs",dat,append=T),
> dbWriteTable(con,"wvs",dat) )
> }
> close(f)
> #cal the time require
> Sys.time()-tim1
>
> #code end.
>
Re: [sqlite] how can I import CSV file into SQLite quickly
Thanks to all give response to help.
This is my solution using the luanguage I familiar.(http://www.r-project.org).
I use the code to read a 11819x807 csv file and it takes 10 minus.I think is
not too slow .(My PC:1.7G,512M RAM)
#code begins
rm(list=ls())
f<-file("D:\\wvsevs_sb_v4.csv","r")#134M
i <- 0
done <- FALSE
library(RSQLite)
con<-dbConnect("SQLite","c:\\sqlite\\database.db3")
tim1<-Sys.time()
while(!done){
i<-i+1
tt<-readLines(f,2500)
if (length(tt)<2500) done <- TRUE
tt<-textConnection(tt)
if (i==1) {
assign("dat",read.table(tt,head=T,sep=",",quote=""));
# to make the variable names elegent
nam<-names(dat);
nam<-gsub("^X.","",nam);
nam<-tolower(gsub(".$","",nam))
names(dat)<-nam
#
}
else assign("dat",read.table(tt,head=F,sep=",",quote=""))
close(tt)
ifelse(dbExistsTable(con, "wvs"),dbWriteTable(con,"wvs",dat,append=T),
dbWriteTable(con,"wvs",dat) )
}
close(f)
#cal the time require
Sys.time()-tim1
#code end.
[sqlite] how can I import CSV file into SQLite quickly
I have a very large CSV file with 1 rows and 100 columns.and the file looks like the following: "a","b","c","d", "1","2","1","3" , "3","2","2","1", .. If I use .import,It seems I have to set the variable names manually . Is there any way to import the whole data file into SQLite quickly? Thank you! ronggui
