Change data.table values in one column for multiple rows

Question

I am trying to change the values of one column for specific rows in a data.table. This works when I do a vector scan but not when I do a binary search.

dtData <- data.table(TickerId = c(1,2,3,4,5), DateTime = c(1,2,3,4,5), Close =     c(100,200,300,400,500), key=c('TickerId', 'DateTime'))
dtQuery <- data.table(TickerId = c(1,4), DateTime = c(1,4))

#Binary search doesn't work - both changed rows now contain 101
dtData[dtQuery, Close:=c(101,401)]

#Vector scan works
dtData[TickerId %in% c(1,4) & DateTime %in% c(1,4), Close:=c(101,401)]

Could someone point out why this might be the case?

Also what would be the best (fastest) way to change values like this in a large data.table?

Thank you.

Answer 1

Does this work?

dtQuery[,newClose:=c(101,401)]
dtData[dtQuery,Close:=newClose]

If so, it is far better than your vector scan, and not just because of speed. The vector scan looks very fragile. With it, what happens if you see a pair (4,1) or if you see the (4,4) before the (1,1)?

Answer 2

Note the different results from

dtData[dtQuery, Close]
#    TickerId DateTime Close
# 1:        1        1   100
# 2:        4        4   400

dtData[TickerId %in% c(1,4) & DateTime %in% c(1,4), Close]
# [1] 100 400

So in order to use binary search, you have to select the Close column

dtData[dtQuery, ][, Close] 

However, assignement does not work in compound queries.