How to update rows in Database with values from data.frame in R conditionally

Question

I've a data.frame in R - matches with some 1000+ rows

names(matches)
[1] "name"  "c_id"  "fname" "lname" "address" "zip_code"    "Weight"

nrow(matches)
[1] 1253

I have a postgresql database table - list_m with following columns

db_name, db_cid, db_weight, processing_status, request_id, fname, mname, lname, etc.

I want to udpate the values of only few columns (db_c_id, db_weight and processing status) in the table using values from the data.frame.

As of now, I am looping over the data.frame to create the update queries and then run the queries.

for(row in 1:nrow(matches) {   
    query1 <- paste0(query1, "UPDATE list_m SET db_name = ",matches$name[row],", db_weight = ",matches$weight[row],",  processing_status = 'MATCHED'
 WHERE request_id=111 AND db_c_id = '", matches$c_id[row], "';")
}

so it basically creates a query1 variable with

 UPDATE list_m SET db_name = 'HILLARY', db_weight = 51.41, processing_status = 'MATCHED' WHERE request_id=111 AND db_c_id = '1015310246';

 UPDATE list_m SET db_name = 'SANDERS', db_weight = 45.16, processing_status = 'MATCHED' WHERE request_id=111 AND db_c_id = '1015120982';

 ...

 ...

 ...

 UPDATE list_m SET db_name = 'OBAMA', db_weight = 67.11, processing_status = 'MATCHED' WHERE request_id=111 AND db_c_id = '1015110111'; 

 UPDATE list_m SET db_name = 'TRUMP', db_weight = 41.22, processing_status = 'MATCHED' WHERE request_id=111 AND db_c_id = '1013024634';

which will then be executed using

dbSendStatement(con, query1)

What I would like is to do this by parameterizing the values.. something like

query2 <- "UPDATE list_m SET db_name=?,db_weight=?,processing_status='MATCHED' WHERE request_id=111 and db_c_id=?";

dbSendStatement(con, query2, matches$name, matches$weight, matches$c_id)

this statement should execute for each row of matches data.frame.

con is the db connection string: > drv <- dbDriver("PostgreSQL") > con <- dbConnect(drv, dbname=config$db$name,host=config$db$host,port=config$db$port,user=config$db$user,password=config$db$pass ) — Avinash Sonee
– Avinash Sonee, Commented Dec 21, 2016 at 14:24

manotheshark · Accepted Answer · 2016-12-21 17:09:34Z

3

This could be closely approximated using sprintf

sql_string <- "UPDATE list_m SET db_name = %s, db_weight = %s,  processing_status = 'MATCHED' WHERE request_id=111 AND db_c_id = '%s';"

dbSendStatement(con, paste(sprintf(sql_string, matches$name, matches$weight, matches$c_id), collapse=""))

edited Dec 21, 2016 at 17:09

answered Dec 21, 2016 at 15:02

manotheshark

4,36520 silver badges31 bronze badges

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

4 Comments

manotheshark Over a year ago

@Parfait changed answer to approximate parameters using sprintf

Parfait Over a year ago

I thought your approach was great. Just needed to adhere to Postgres syntax and advise OP to export dataframe as temp table, altogether avoiding for loop.

SunWuKung Over a year ago

This approach is great! Would it be possible with named parameters instead of just relying on the parameter order?

manotheshark Over a year ago

@SunWuKung the command could be wrapped in a function which would make it possible to used named parameters. However, the function will revert back to parameter order if the parameter names are not used when calling the function.

Collectives™ on Stack Overflow

How to update rows in Database with values from data.frame in R conditionally

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related