0

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)?

rkraft
  • 495
  • 4
  • 16

1 Answers1

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.

Jakub.Novotny
  • 2,912
  • 2
  • 6
  • 21
  • 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? – rkraft Aug 12 '20 at 15:38
  • 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 https://stackoverflow.com/questions/224732/sql-update-from-one-table-to-another-based-on-a-id-match – Jakub.Novotny Aug 12 '20 at 16:37
  • What are your thoughts? Does it solve your problem? – Jakub.Novotny Aug 14 '20 at 14:02