[R-sig-DB] SQLite - inserting a row conditional on its existence

[Lescai, Francesco]

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