e_rows_update.tbl_dbi {editbl}

rows_update implementation for DBI backends.

Description

rows_update implementation for DBI backends.

Usage

## S3 method for class 'tbl_dbi'
e_rows_update(
  x,
  y,
  by = NULL,
  match = NULL,
  ...,
  copy = FALSE,
  in_place = FALSE
)

Arguments

x, 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 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.

match

named list consisting out of two equal length data.frame's with columns defined in by. This allows for updates of columns defined in by.

...

Other parameters passed onto methods.

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.

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.

Details

Mainly a wrapper around rows_update. Allows for specific implementations should the behavior differ from what's needed by editbl. Reason for separate method is to avoid conflicts on package loading.

Value

An object of the same type as x. The order of the rows and columns of x is preserved as much as possible. The output has the following properties:

• rows_update() and rows_patch() preserve the number of rows; rows_insert(), rows_append(), and rows_upsert() return all existing rows and potentially new rows; rows_delete() returns a subset of the rows.

• Columns are not added, removed, or relocated, though the data may be updated.

• Groups are taken from x.

• Data frame attributes are taken from x.

If in_place = TRUE, the result will be returned invisibly.

Author(s)

Jasper Schelfhout

Examples

library(dplyr)

# Set up a test table
conn <- DBI::dbConnect(RSQLite::SQLite(), ":memory:")
artists_df <- data.frame(
         ArtistId = c(1,2),
         Name = c("AC/DC", "The Offspring")
)
DBI::dbWriteTable(conn, "Artist", artists_df)     

# Update rows without changing the key.
artists <- tbl(conn, "Artist")
DBI::dbBegin(conn)
y <- data.frame(ArtistId = 1, Name = "DC/AC")
e_rows_update(
     x = artists,
     y = y,
     by = "ArtistId",
     in_place = TRUE)
DBI::dbRollback(conn)

# Update key values of rows.
DBI::dbBegin(conn)
y <- data.frame(ArtistId = 999, Name = "DC/AC")
match <- list(
   x = data.frame("ArtistId" = 1),
   y = data.frame("ArtistId" = 999)
)
e_rows_update(
    x = artists,
    y = y,
    match = match,
    by = "ArtistId",
    in_place = TRUE)
DBI::dbRollback(conn)
DBI::dbDisconnect(conn)