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.