CODEWITHSUNDEEP
rsql-serversql-updatesqldf
janf
janf

Reputation: 81

Update values using multiple columns from one table and a single column from another table

I have the following table lc:

a   b   c   d 
co  NA  co  1
co  NA  co  84
us  co  us  6
NA  co  NA  54
us  NA  NA  4

And another table ro

d   value
bb  1
co  0.5
mn  0.03
us  2

I want to update the values in lc with the values in ro to get the following:

a    b    c    d 
0.5  NA   0.5  1
0.5  NA   0.5  84
2    0.5  2    6
NA   0.5  NA   54
2    NA   NA   4

I have the code:

lc <- sqldf(c("UPDATE lc SET a = (SELECT ro.value FROM ro WHERE lc.a = ro.d)",
    "SELECT * FROM main.lc")
    )

And that obviously updates only the column a, but not the others. How can I update the remaining columns in the same statement? I have looked at posts that address the UPDATE query in SQL, but all of them just compare one column from one table to one column from another table.

This is just an example, I actually have 40 columns to be updated in lc with millions of rows and a b c values can be different from co and us, so I need it to be very efficient.

Upvotes: 1

Views: 912

Answers (2)

akrun
akrun

Reputation: 887158

With base R, we use a named vector to match the values in the character columns in 'lc' and replace those values with the correpsonding 'value' from 'ro'

lc[1:3] <- setNames(ro$value, ro$d)[as.matrix(lc[1:3])]
lc
#    a   b   c  d
#1 0.5  NA 0.5  1
#2 0.5  NA 0.5 84
#3 2.0 0.5 2.0  6
#4  NA 0.5  NA 54
#5 2.0  NA  NA  4

data

lc <- structure(list(a = c("co", "co", "us", NA, "us"), b = c(NA, NA, 
"co", "co", NA), c = c("co", "co", "us", NA, NA), d = c(1L, 84L, 
6L, 54L, 4L)), class = "data.frame", row.names = c(NA, -5L))

ro <- structure(list(d = c("bb", "co", "mn", "us"), value = c(1, 0.5, 
0.03, 2)), class = "data.frame", row.names = c(NA, -4L))

Upvotes: 0

mkRabbani
mkRabbani

Reputation: 16908

You can try this below script-

SELECT B.value a,C.value b,D.value c, A.d 
FROM lc A
LEFT JOIN ro B ON A.a = B.d
LEFT JOIN ro C ON A.b = C.d
LEFT JOIN ro D ON A.C = D.d

Output is-

a       b       c       d
0.50    NULL    0.50    1
0.50    NULL    0.50    84
2.00    0.50    2.00    6
NULL    0.50    NULL    54
2.00    NULL    NULL    4

Upvotes: 5

Related Questions