R equivalent of SQL update statement

Question

I use the below statement to update to the postgreSQL db using the following statement

update users
set col1='setup', 
col2= 232
where username='rod';

Can anyone guide how to do similar to using R ?I am not good in R

Thanks in advance for the help

Not its the database, i am looking a R code to hit and update in DB table. — Rod
– Rod, Commented Nov 20, 2019 at 17:32
Hmmm...Why are you not using SQL to interact with a DB table? — Parfait
– Parfait, Commented Nov 20, 2019 at 17:34
Hmmm... what requirement is that? How are you connecting to database then? Please explain more of your entire issue. Are you importing the DB table into R then pushing back out? I ask because an R solution has been posted. To directly update you must use language of database (i.e., SQL). — Parfait
– Parfait, Commented Nov 20, 2019 at 17:36

sempervent · Accepted Answer · 2019-11-25 13:07:21Z

2

Since you didn't provide any data, I've created some here.

users <- data.frame(username = c('rod','stewart','happy'), col1 = c(NA_character_,'do','run'), col2 = c(111,23,145), stringsAsFactors = FALSE)

To update using base R:

users[users$username == 'rod', c('col1','col2')] <- c('setup', 232)

If you prefer the more explicit syntax provided by the data.table package, you would execute:

library(data.table)
setDT(users)
users[username == 'rod', `:=`(col1 = 'setup', col2 = 232)]

To update your database through RPostgreSQL, you will first need to create Database Connection, and then simply store your query in a string, e.g.

con <- dbConnect('PostgreSQL', dbname = <your database name>, user=<user>, password= <password>)
statement <- "update <schema>.users set col1='setup', col2= 232 where username='rod';"
dbGetQuery(con, statement)
dbDisconnect()

Note depending upon your PostgreSQL configs, you may need to also set your search path dbGetQuery(con, 'set search_path = <schema>;')

I'm more familiar with RPostgres, so you may want to double check the syntax and vignettes of the PostgreSQL package.

EDIT: Seems like RPostgreSQL prefers dbGetQuery to send updates and commands rather than dbSendQuery

edited Nov 25, 2019 at 13:07

answered Nov 20, 2019 at 17:14

sempervent

9034 gold badges11 silver badges23 bronze badges

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

6 Comments

Rod Over a year ago

Thanks for the response, I am looking to directly update the tables to DB using R? not to the local datasets.

Parfait Over a year ago

To directly update a DB table you must use language of database (i.e., SQL). Do note: R (general-purpose language) can run SQL (special-purpose language).

Rod Over a year ago

ok I found the similar code in other stack stackoverflow.com/questions/15099507/… But not able to fit to my requirement as state above.? could anyone help

user10917479 Over a year ago

I would recommend spending some time in db.rstudio.com

Rod Over a year ago

thanks, I am not able to figure out for the update statement. Anyone can help me on this ? pls..

|

Collectives™ on Stack Overflow

R equivalent of SQL update statement

1 Answer 1

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related