Search code examples
rrodbcr-dbirpostgresqlrpostgres

Update only certain columns in SQL parent table from R

Let's say I have mtcars in a postgres server.

library("RODBC")
library("odbc")
library("RPostgres")
con = dbConnect(RPostgres::Postgres(),dbname="example_db",port = 5432,user = "postgres", password = "db_password")

I can update the mtcars table with:

good_old_mtcars = data.frame(cbind("ids" = seq(1:nrow(mtcars)), mtcars))
dbWriteTable(con1, "example_db_mtcars", good_old_mtcars, temporary = FALSE, append=FALSE, overwrite=TRUE)

It happens that some columns may need updating. Eg. updating qsec with some calculation

good_old_mtcars$qsec = good_old_mtcars$qsec * sqrt(good_old_mtcars$wt)

How could I update only the qsec column without deleting and uploading the whole table again?

** mtcars is a parent table and the ids variable is used by other tables so re-uploading the whole data frame breaks other things in the database :(

There is a solution here referenced here but it is 15 years old. Any better way to do it?

Solution

  • You can use rows_update() with the in_place param set to TRUE, like this:

    
library(dplyr)
good_old_mtcars = tbl(con, "example_db_mtcars")

rows_update(
  x = good_old_mtcars,
  y = good_old_mtcars %>% mutate(qsec = sqrt(qsec)) %>% select(ids, qsec),
  by = "ids",
  unmatched = "ignore",
  in_place = TRUE
)