sqldf statement delete rows syntax error?

Question

Hi stackoverflow experts,

I´m trying to use sqldf to delete rows in a r table such as this:

structure(list(Similarity = c(999L, 888L, 756L, 879L, 567L, 567L), Peak = c(797L, 833L,999L, 798L, 834L, 444L), Formula = structure(c(4L,3L, 4L, 1L, 2L, 2L), .Label = c("C12H26S", "C16H19NO", "C2H8O2Si","C9H13NO2"), class = "factor")), .Names = c("Similarity", "Peak","Formula"), class = "data.frame", row.names = c(NA, -6L))

My goal is to delete rows where "SI" appears on the formula column like this:

structure(list(Similarity = c(999L, 756L, 879L, 567L, 567L), 
Peak = c(797L, 999L, 798L, 834L, 444L), Formula = structure(c(3L, 
3L, 1L, 2L, 2L), .Label = c("C12H26S", "C16H19NO", "C9H13NO2"
), class = "factor")), .Names = c("Similarity", "Peak", "Formula"
), class = "data.frame", row.names = c(NA, -5L))

I have tried a sqldf statement:

sqldf("DELETE * FROM PO_raw WHERE Formula='Si'")

But, of course, it doesn´t work since there is no row with only "Si". I´m a beginner with sqldf and it seems to me that this is probably an error in the syntax. I have search in the web but didn´t found any example.

Any suggestion? Is this even possible with sqldf?

cheers,

Francisco

Community · Accepted Answer · 2017-05-23 12:25:17Z

4

Use LIKE and a wildcard, like this:

> sqldf(c("DELETE FROM PO_raw WHERE Formula like '%Si'", "select * from main.PO_raw"))
  Similarity Peak  Formula
1        999  797 C9H13NO2
2        756  999 C9H13NO2
3        879  798  C12H26S
4        567  834 C16H19NO
5        567  444 C16H19NO

(BTW: Interesting question about LIKE)

edited May 23, 2017 at 12:25

CommunityBot

11 silver badge

answered Dec 10, 2012 at 15:19

ROLO

4,22327 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Francisco Over a year ago

Rolo, I have tried your suggestion but the message it appears is - Error in sqliteExecStatement(con, statement, bind.data) : RS-DBI driver: (error in statement: near "*": syntax error). Any suggestion?

G. Grothendieck Over a year ago

delete does not take a * after it and also does not return anything so follow it with a select statement as shown in the revised version.

Francisco Over a year ago

Thanks Grothendieck. This solves my problem if Si is a suffix. But it is possible to delete with sqldf if its anywhere like e.g. C9H13NOSi3?

Francisco Over a year ago

I found it, I just need to add a extra % to SI = %SI%

Collectives™ on Stack Overflow

sqldf statement delete rows syntax error?

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related