How to update cells in a frame from another frame

If anyone could help me out with the question below would be great.

dt_x <- as.data.table(matrix(c("A","B","C","D",0,0,0,0), ncol=2))
dt_y <- as.data.table(matrix(c("B","C","D",2,2,3), ncol=2))

Column V1 functions as joining key
I wanna update dt_x row V1 = "B" its value in column V2 (0) with dt_y its
corresponding (join) value for V2, being 2. Leave the other rows unchanged.
So I am looking for the statement(s) that result in an updated dt_x showing:
V1 V2
A 0
B 2
C 0
D 0
Any straightforward solution is much appreciated. When this is via UPDATE and JOIN in data.table is also fine, but I cannot figure out the equivalent of the SQL equivalent I am looking for
(UPDATE dt_x
SET dt_x.V2 = dt_y.V2
FROM dt_x INNER JOIN dt_y ON dt_x.V1 = dt_y.V1
WHERE dt_x.V1 = "B")

Thanks a lot.

dt_x[dt_x$V1=="B"] <- dt_y[dt_y$V1=="B"]

does the job as long as there are no other columns. You can identify the column by position as well to just replace that one cell:

dt_x[dt_x$V1=="B",2] <- dt_y[dt_y$V1=="B",2]

1 Like

This topic was automatically closed 21 days after the last reply. New replies are no longer allowed.

If you have a query related to it or one of the replies, start a new topic and refer back with a link.