[R-sig-DB] SQLite - inserting a row conditional on its existence
Lescai, Francesco
|@|e@c@| @end|ng |rom uc|@@c@uk
Tue Mar 20 14:00:06 CET 2012
Thanks Sean,
I tried that one as well (popped out on a google search among the first) but
> dbBeginTransaction(db)
[1] TRUE
> query <- paste('INSERT INTO "variation"',
+ 'VALUES(NULL, :name, :reference_allele, :validation_status)',
+ 'WHERE NOT EXISTS (SELECT * FROM variation WHERE name = :name);')
> dbGetPreparedQuery(db, query, bind.data=variants)
Error in sqliteExecStatement(con, statement, bind.data) :
RS-DBI driver: (error in statement: near "WHERE": syntax error)
it seems to be a syntax error in both cases.
not sure if it is an SQLite syntax error, or a DBI/RSQLite syntax error.
cheers,
Francesco
On 20 Mar 2012, at 12:51, Sean Davis wrote:
On Tue, Mar 20, 2012 at 8:28 AM, Lescai, Francesco <f.lescai using ucl.ac.uk<mailto:f.lescai using ucl.ac.uk>> wrote:
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?
Hi, Francesco.
Not all SQL implementations are the same. Check this link:
http://stackoverflow.com/questions/531035/how-to-do-if-not-exists-in-sqlite
Sean
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><mailto:f.lescai using ucl.ac.uk<mailto:f.lescai using ucl.ac.uk>>
phone: +44.(0)207.905.2274<tel:%2B44.%280%29207.905.2274>
[ext: 2274]
--------------------------------------------------------------------------------
[[alternative HTML version deleted]]
_______________________________________________
R-sig-DB mailing list -- R Special Interest Group
R-sig-DB using r-project.org<mailto:R-sig-DB using r-project.org>
https://stat.ethz.ch/mailman/listinfo/r-sig-db
---------------------------------------------------------------------------------
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