[R-sig-DB] SQLite - inserting a row conditional on its existence
Lescai, Francesco
|@|e@c@| @end|ng |rom uc|@@c@uk
Tue Mar 20 13:28:28 CET 2012
Hi there,
I'm pretty new to the use of R with a newly created schema SQLite database.
I've been looking to the vignettes and to some examples, but I couldn't find a pretty basic task: i.e. inserting a row in a table only if it doesn't exist.
if it does, I would either update or do nothing.
this is a simple table in my database
> res <- dbSendQuery(db, "SELECT * from variation")
> fetch(res)
variation_id name reference_allele validation_status
1 1 rs123 A not_validated
2 2 rs234 C validated
3 3 rs345 T not_validated
and this the small test dataframe I'd like to insert data from
variants=data.frame(variation_id=c(1,2,3),name=c("rsNN","rsXX", "rs123"), reference_allele=c("A","C","T"), validation_status=c("not_validated","validated","not_validated"))
based on SQL commands I was tempted to write something like
dbBeginTransaction(db)
query <- paste('IF NOT EXISTS (SELECT * FROM variation WHERE name = :name)',
'INSERT INTO "variation"',
"VALUES(NULL, :name, :reference_allele, :validation_status);")
dbGetPreparedQuery(db, query, bind.data=variants)
but I got the error
Error in sqliteExecStatement(con, statement, bind.data) :
RS-DBI driver: (error in statement: near "IF": syntax error)
Anyone who could point me on the right direction?
thanks very much,
Francesco
> sessionInfo()
R Under development (unstable) (2012-01-20 r58146)
Platform: x86_64-apple-darwin9.8.0/x86_64 (64-bit)
locale:
[1] C/en_US.UTF-8/C/C/C/C
attached base packages:
[1] stats graphics grDevices utils datasets methods base
other attached packages:
[1] RSQLite_0.11.1 DBI_0.2-5
loaded via a namespace (and not attached):
[1] tools_2.15.0
---------------------------------------------------------------------------------
Francesco Lescai, PhD, EDBT
Senior Research Associate in Genome Analysis
University College London
Faculty of Population Health Sciences
Dept. Genes, Development & Disease
ICH - Molecular Medicine Unit, GOSgene team
30 Guilford Street
WC1N 1EH London UK
email: f.lescai using ucl.ac.uk<mailto:f.lescai using ucl.ac.uk>
phone: +44.(0)207.905.2274
[ext: 2274]
--------------------------------------------------------------------------------
[[alternative HTML version deleted]]
More information about the R-sig-DB
mailing list