Update only certain columns in SQL parent table from R

Question

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?

langtang · Accepted Answer · 2023-11-26 15:47:56Z

1

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
)

answered Nov 26, 2023 at 15:47

langtang

25.3k1 gold badge14 silver badges32 bronze badges

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

Collectives™ on Stack Overflow

Update only certain columns in SQL parent table from R

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related