These are methods for the dplyr rows_insert(), rows_append(),
rows_update(), rows_patch(), rows_upsert(), and rows_delete()
generics.
When in_place = TRUE these verbs do not generate SELECT queries, but
instead directly modify the underlying data using INSERT, UPDATE, or
DELETE operators. This will require that you have write access to
the database: the connection needs permission to insert, modify or delete
rows, but not to alter the structure of the table.
The default, in_place = FALSE, generates equivalent lazy tables (using
SELECT queries) that allow previewing the result without actually
modifying the underlying table on the database.
# S3 method for class 'tbl_lazy'
rows_insert(
x,
y,
by = NULL,
...,
conflict = c("error", "ignore"),
copy = FALSE,
in_place = FALSE,
returning = NULL,
method = NULL
)
# S3 method for class 'tbl_lazy'
rows_append(x, y, ..., copy = FALSE, in_place = FALSE, returning = NULL)
# S3 method for class 'tbl_lazy'
rows_update(
x,
y,
by = NULL,
...,
unmatched = c("error", "ignore"),
copy = FALSE,
in_place = FALSE,
returning = NULL
)
# S3 method for class 'tbl_lazy'
rows_patch(
x,
y,
by = NULL,
...,
unmatched = c("error", "ignore"),
copy = FALSE,
in_place = FALSE,
returning = NULL
)
# S3 method for class 'tbl_lazy'
rows_upsert(
x,
y,
by = NULL,
...,
copy = FALSE,
in_place = FALSE,
returning = NULL,
method = NULL
)
# S3 method for class 'tbl_lazy'
rows_delete(
x,
y,
by = NULL,
...,
unmatched = c("error", "ignore"),
copy = FALSE,
in_place = FALSE,
returning = NULL
)A lazy table.
For in_place = TRUE, this must be a table instantiated with tbl() or
compute(), not to a lazy query. The remote_name() function is used to
determine the name of the table to be updated.
A lazy table, data frame, or data frame extensions (e.g. a tibble).
An unnamed character vector giving the key columns. The key columns
must exist in both x and y. Keys typically uniquely identify each row,
but this is only enforced for the key values of y when rows_update(),
rows_patch(), or rows_upsert() are used.
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.
For rows_insert(), how should keys in y that conflict
with keys in x be handled? A conflict arises if there is a key in y
that already exists in x.
One of:
"error", the default, is not supported for database tables. To get the
same behaviour add a unique index on the by columns and use
rows_append().
"ignore" will ignore rows in y with keys that conflict with keys in
x.
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.
Should x be modified in place? If FALSE will
generate a SELECT query that returns the modified table; if TRUE
will modify the underlying table using a DML operation (INSERT, UPDATE,
DELETE or similar).
Columns to return. See get_returned_rows() for details.
A string specifying the method to use. This is only relevant for
in_place = TRUE.
For rows_update(), rows_patch(), and rows_delete(),
how should keys in y that are unmatched by the keys in x be handled?
One of:
"error", the default, is not supported for database tables. Add a
foreign key constraint on the by columns of y to let the database
check this behaviour for you.
"ignore" will ignore rows in y with keys that are unmatched by the
keys in x.
A new tbl_lazy of the modified data.
With in_place = FALSE, the result is a lazy query that prints visibly,
because the purpose of this operation is to preview the results.
With in_place = TRUE, x is returned invisibly,
because the purpose of this operation is the side effect of modifying rows
in the table behind x.
library(dplyr)
con <- DBI::dbConnect(RSQLite::SQLite(), ":memory:")
DBI::dbExecute(con, "CREATE TABLE Ponies (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT,
cutie_mark TEXT
)")
#> [1] 0
ponies <- tbl(con, "Ponies")
applejack <- copy_inline(con, data.frame(
name = "Apple Jack",
cutie_mark = "three apples"
))
# The default behavior is to generate a SELECT query
rows_insert(ponies, applejack, conflict = "ignore")
#> Matching, by = "name"
#> # Source: SQL [1 x 3]
#> # Database: sqlite 3.46.0 [:memory:]
#> id name cutie_mark
#> <lgl> <chr> <chr>
#> 1 NA Apple Jack three apples
# And the original table is left unchanged:
ponies
#> # Source: table<`Ponies`> [0 x 3]
#> # Database: sqlite 3.46.0 [:memory:]
#> # ℹ 3 variables: id <int>, name <chr>, cutie_mark <chr>
# You can also choose to modify the table with in_place = TRUE:
rows_insert(ponies, applejack, conflict = "ignore", in_place = TRUE)
#> Matching, by = "name"
# In this case `rows_insert()` returns nothing and the underlying
# data is modified
ponies
#> # Source: table<`Ponies`> [1 x 3]
#> # Database: sqlite 3.46.0 [:memory:]
#> id name cutie_mark
#> <int> <chr> <chr>
#> 1 1 Apple Jack three apples