How to update certain rows using UPDATE function using sqldf R package?

I have two tables: dat1 and dat2.

names(dat1):
id, filename, kd_estimated, kd_95CI_low, kd_95CI_upp, start_time, end_time, max_response, min_response   , RMSE, resp_95CI_low, resp_95CI_upp



  names(dat2) = names(dat1)

The data looks like the following:

1, A1_a1H12.xls.txt, 0.00172, 0.0017, 0.00174, 450, 600, 0.267, 0.204, 0.00256, 0.229, 0.229

I want to update dat1 with dat2 when the file names are the same. Below is my code:

mydata_up <- sqldf(c("UPDATE dat1 SET kd_estimated = dat2.kd_estimated FROM dat2 WHERE dat2.filename = dat1.filename", "SELECT * FROM dat1"))

However, I always get the following error:

“Error: near “FROM”: syntax error”

No clue 🙁 Thank you for the help.

Answer

In many databases, you could use a correlated subquery for this:

UPDATE dat1 SET kd_estimated = (
    SELECT dat2.kd_estimated 
    FROM dat2 
    WHERE dat2.filename = dat1.filename
)

If there may be missing matches in dat2, then:

UPDATE dat1 SET kd_estimated = (
    SELECT dat2.kd_estimated 
    FROM dat2 
    WHERE dat2.filename = dat1.filename
)
WHERE EXISTS (
    SELECT 1
    FROM dat2 
    WHERE dat2.filename = dat1.filename
)