Skip to content

These functions generate the SQL used in rows_*(in_place = TRUE).

Usage

sql_query_insert(
  con,
  x_name,
  y,
  by,
  ...,
  conflict = c("error", "ignore"),
  returning_cols = NULL,
  method = NULL
)

sql_query_append(con, x_name, y, ..., returning_cols = NULL)

sql_query_update_from(
  con,
  x_name,
  y,
  by,
  update_values,
  ...,
  returning_cols = NULL
)

sql_query_upsert(
  con,
  x_name,
  y,
  by,
  update_cols,
  ...,
  returning_cols = NULL,
  method = NULL
)

sql_query_delete(con, x_name, y, by, ..., returning_cols = NULL)

Arguments

con

Database connection.

x_name

Name of the table to update.

y

A lazy tbl.

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.

...

Other parameters passed onto methods.

conflict

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, will error if there are any keys in y that conflict with keys in x.

  • "ignore" will ignore rows in y with keys that conflict with keys in x.

returning_cols

Optional. Names of columns to return.

method

Optional. The method to use.

update_values

A named SQL vector that specify how to update the columns.

update_cols

Names of columns to update.

Value

A SQL query.

Details

Insert Methods

"where_not_exists"

The default for most databases.

INSERT INTO x_name
SELECT *
FROM y
WHERE NOT EXISTS <match on by columns>

"on_conflict"

Supported by:

  • Postgres

  • SQLite

This method uses the ON CONFLICT clause and therefore requires a unique index on the columns specified in by.

Upsert Methods

"merge"

The upsert method according to the SQL standard. It uses the MERGE statement

MERGE INTO x_name
USING y
  ON <match on by columns>
WHEN MATCHED THEN
  UPDATE SET ...
WHEN NOT MATCHED THEN
  INSERT ...

"on_conflict"

Supported by:

  • Postgres

  • SQLite

This method uses the ON CONFLICT clause and therefore requires a unique index on the columns specified in by.

"cte_update"

Supported by:

  • Postgres

  • SQLite

  • Oracle

The classical way to upsert in Postgres and SQLite before support for ON CONFLICT was added. The update is done in a CTE clause and the unmatched values are then inserted outside of the CTE.

Examples

lf <- lazy_frame(
  carrier = c("9E", "AA"),
  name = c("Endeavor Air Inc.", "American Airlines Inc."),
  con = simulate_postgres()
)

sql_query_upsert(
  simulate_postgres(),
  ident("airlines"),
  lf,
  by = "carrier",
  update_cols = "name"
)
#> <SQL> INSERT INTO `airlines` (`carrier`, `name`)
#> SELECT *
#> FROM `df` AS `...y`
#> WHERE true
#> ON CONFLICT  (`carrier`)
#> DO UPDATE
#> SET `name` = `excluded`.`name`