Multi row and column update using R in postgres database

Question

Problem: I want to update values (in multiple rows and columns) of a table in a postgres database using R.

I know that the sql update statement can be something like this but I assume looping over a set of such queries is inefficient:

UPDATE table
SET col1 = value1, col2 = value2, ...
WHERE col1 = "some-value"

Question: Is there a function available to only update particular rows (and potentially only a subset of the columns) of the table (similar to dbWriteTable)? If not, can you think of an efficient way/sql query of updating multiple rows in postgres and how to hand over the R object to the sql query?

EDIT: Assuming I have a foreign key and I don't want to turn on the ON DELETE CASCADE option, how could I efficiently update values in multi rows and columns for the parent table (I only want to update parent table, not child table)?

Jakub.Novotny · Accepted Answer · 2020-08-12 15:32:58Z

0

First, I would read in the data that needs to be updated from SQL to R. Second, I would delete the data to be updated in SQL. Third, I would append the updated data from R to SQL.

answered Aug 12, 2020 at 15:32

Jakub.Novotny

3,0772 gold badges9 silver badges25 bronze badges

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

3 Comments

rkraft Over a year ago

thanks for reply! assuming one/more column(s) has a foreign key constraint such that you cannot delete the data (for some reasons, I don't want to have ON DELETE CASCADE option turned on) and I only want to update the parent table (not the child table). do you have an idea how to approach this?

Jakub.Novotny Over a year ago

You can dbWriteTable the df containing the updated data as a temporary table and then update a table based on data in another table based on an ID match as described e.g. here stackoverflow.com/questions/224732/…

Jakub.Novotny Over a year ago

What are your thoughts? Does it solve your problem?

Collectives™ on Stack Overflow

Multi row and column update using R in postgres database

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related