Updating database tables

These methods provide a framework for manipulating individual rows in existing tables. All operations expect that both existing and new data are presented in two compatible tbl objects.

If y lives on a different data source than x, it can be copied automatically by setting copy = TRUE, just like for dplyr::left_join().

On mutable backends like databases, these operations manipulate the underlying storage. In contrast to all other operations, these operations may lead to irreversible changes to the underlying database. Therefore, in-place updates must be requested explicitly with in_place = TRUE. By default, an informative message is given. Unlike compute() or copy_to(), no new tables are created.

The sql_rows_*() functions return the SQL used for the corresponding rows_*() function with in_place = FALSE. y needs to be located on the same data source as x.

# S3 method for tbl_dbi
rows_insert(x, y, by = NULL, ..., in_place = NULL, copy = FALSE, check = NULL)

# S3 method for tbl_dbi
rows_update(x, y, by = NULL, ..., in_place = NULL, copy = FALSE, check = NULL)

sql_rows_insert(x, y, ...)

sql_rows_update(x, y, by, ...)

Arguments

x	A pair of data frames or data frame extensions (e.g. a tibble). y must have the same columns of x or a subset.
y	A pair of data frames or data frame extensions (e.g. a tibble). y must have the same columns of x or a subset.
by	An unnamed character vector giving the key columns. The key values must uniquely identify each row (i.e. each combination of key values occurs at most once), and the key columns must exist in both x and y. By default, we use the first column in y, since the first column is a reasonable place to put an identifier variable.
...	Other parameters passed onto methods.
in_place	Should x be modified in place? This argument is only relevant for mutable backends (e.g. databases, data.tables). When TRUE, a modified version of x is returned invisibly; when FALSE, a new object representing the resulting changes is returned.
copy	If x and y are not from the same data source, and copy is TRUE, then y will be copied into the same src as x. This allows you to join tables across srcs, but it is a potentially expensive operation so you must opt into it.
check	Set to TRUE to always check keys, or FALSE to never check. The default is to check only if in_place is TRUE or NULL. Currently these checks are no-ops and need yet to be implemented.

Value

A tbl object of the same structure as x. If in_place = TRUE, the underlying data is updated as a side effect, and x is returned, invisibly.

Examples

data <- dbplyr::memdb_frame(a = 1:3, b = letters[c(1:2, NA)], c = 0.5 + 0:2)
data
#> # Source:   table<dbplyr_001> [?? x 3]
#> # Database: sqlite 3.34.1 [:memory:]
#>       a b         c
#>   <int> <chr> <dbl>
#> 1     1 a       0.5
#> 2     2 b       1.5
#> 3     3 NA      2.5

try(rows_insert(data, tibble::tibble(a = 4, b = "z")))
#> Error : `x` and `y` must share the same src, set `copy` = TRUE (may be slow).
rows_insert(data, tibble::tibble(a = 4, b = "z"), copy = TRUE)
#> Result is returned as lazy table. Use `in_place = FALSE` to mute this message, or `in_place = TRUE` to write to the underlying table.
#> # Source:   lazy query [?? x 3]
#> # Database: sqlite 3.34.1 [:memory:]
#>       a b         c
#>   <dbl> <chr> <dbl>
#> 1     1 a       0.5
#> 2     2 b       1.5
#> 3     3 NA      2.5
#> 4     4 z      NA  
rows_update(data, tibble::tibble(a = 2:3, b = "w"), copy = TRUE, in_place = FALSE)
#> # Source:   lazy query [?? x 3]
#> # Database: sqlite 3.34.1 [:memory:]
#>       a b         c
#>   <int> <chr> <dbl>
#> 1     1 a       0.5
#> 2     2 w       1.5
#> 3     3 w       2.5

rows_insert(data, dbplyr::memdb_frame(a = 4, b = "z"), in_place = TRUE)
data
#> # Source:   table<dbplyr_001> [?? x 3]
#> # Database: sqlite 3.34.1 [:memory:]
#>       a b         c
#>   <int> <chr> <dbl>
#> 1     1 a       0.5
#> 2     2 b       1.5
#> 3     3 NA      2.5
#> 4     4 z      NA  
rows_update(data, dbplyr::memdb_frame(a = 2:3, b = "w"), in_place = TRUE)
data
#> # Source:   table<dbplyr_001> [?? x 3]
#> # Database: sqlite 3.34.1 [:memory:]
#>       a b         c
#>   <int> <chr> <dbl>
#> 1     1 a       0.5
#> 2     2 w       1.5
#> 3     3 w       2.5
#> 4     4 z      NA