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

[Sean Davis]

On Tue, Mar 20, 2012 at 9:00 AM, Lescai, Francesco <f.lescai using ucl.ac.uk>wrote:

> 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.
>
>
This is a SQLite syntax error.  An insert query cannot include a where
clause, I don't think.  You might try (untested):

INSERT OR IGNORE INTO "variation" VALUES(NULL, :name, :reference_allele,
:validation_status).

You'll need to have a unique constraint in place for this to work as
expected.  See here:

http://www.sqlite.org/lang_insert.html

and here:

http://www.sqlite.org/lang_conflict.html


Sean



> 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]]
>
> _______________________________________________
> R-sig-DB mailing list -- R Special Interest Group
> R-sig-DB using r-project.org
> https://stat.ethz.ch/mailman/listinfo/r-sig-db
>

	[[alternative HTML version deleted]]