FR: UPDATE- and UPSERT-like functionality

[krlmlr]

Sometimes I need to update a bunch of observations in a target table with information from a second table. Currently, I'm doing something like

library(tibble)
library(dplyr)

original <- tribble(
  ~id, ~attr1, ~attr2,
    1,      1,      3,
    2,      2,      4)

new <- tribble(
  ~id, ~attr1, ~attr2,
  2,      5,      6)

combined <-
  original %>%
  left_join(new, by = "id") %>%
  transmute(id,
            attr1 = coalesce(attr1.y, attr1.x),
            attr2 = coalesce(attr2.y, attr2.x))

It would be great if dplyr offered a verb that does this for me. Unlike #1736 and #1216, that verb would create a new table. The code above implements UPDATE, if full_join() is used instead, it's more like UPSERT.

[krlmlr]

@hadley: Is that something we want here or in tidyr?

[hadley]

This feels more like dplyr to me, although there's clearly some connection to tidyverse/tidyr#183

[hadley]

Do you think a single verb upsert() would be sufficient? I imagine it would have the same syntax as a join function, although you'd have to supply the keys.

Might need some variant as to whether an NA on the RHS should be treated as a literal NA, or just means to leave the LHS value as is.

[krlmlr]

I think a single verb that always does a full join should be sufficient. See #1792 for a discussion about keys. We'd have to assume (or check) that the join keys are unique for both datasets (#1619).

Treating NA on the RHS as literal NA feels difficult to implement for SQL sources. All in all, because this functionality can be implemented completely using existing dplyr verbs, it may be more suitable for tidyr. (tidyverse/tidyr#183 contains a lot more discussion, too.)

[hadley]

Ok, lets keep discussion there