CODEWITHSUNDEEP
sqlr
Rod
Rod

Reputation: 75

R equivalent of SQL update statement

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

Upvotes: 0

Views: 2131

Answers (1)

sempervent
sempervent

Reputation: 893

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

Upvotes: 2

Related Questions