Updating values in a database using R does not work

Question

I try to update a row in my sql database using R. Therefore I use the following statement:

 s <- sprintf("UPDATE `match_data` SET `distance` = '%a' WHERE 'Match.ID' = '%a'", distance, id)
 check <- dbGetQuery(con, s)

Where distance and id are both numeric values. And the columns in which I try to update the values are both of type bigint(10).

When I run the query I do not get an error. However, the values do not seem to update. Any thought where this goes wrong?

Does the SQL statement works when submitted directly to the database (not through R)? Do you have the required permissions on the database? What kind of database are you using? — Thierry, Mar 30 '16 at 12:51
i think `dbGetQuery` can operate only with `select` try `dbSendQuery` — Batanichek, Mar 30 '16 at 12:58
@Thierry: yes if I do: UPDATE `match_data` SET `distance` =12 WHERE 'Match.ID' =1283674 it works — Frank Gerritsen, Mar 30 '16 at 14:02
you get some errors ? if data simply not updated may be dbCommit needed? — Batanichek, Mar 30 '16 at 14:06
also may be your not need `'` in `'%a'` because you havent it in your `UPDATE 'match_data' SET 'distance' =12 WHERE 'Match.ID' =1283674` — Batanichek, Mar 30 '16 at 14:10

score 1 · Answer 1 · answered Jun 29 '17 at 21:35

The dbGetQuery only works for SELECT statements. For data manipulation (i.e. UPDATE, DELETE, INSERT INTO, DROP TABLE,..) you may use dbSendStatement or dbExecute which is easier to use.

You may check the doc for more info. https://cran.r-project.org/web/packages/DBI/DBI.pdf

Updating values in a database using R does not work

1 Answers1