sql_query_insert: Generate SQL for Insert, Update, Upsert, and Delete

In hadley/dbplyr: A 'dplyr' Back End for Databases

Generate SQL for Insert, Update, Upsert, and Delete

Description

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

Usage

sql_query_insert(
  con,
  table,
  from,
  insert_cols,
  by,
  ...,
  conflict = c("error", "ignore"),
  returning_cols = NULL,
  method = NULL
)

sql_query_append(con, table, from, insert_cols, ..., returning_cols = NULL)

sql_query_update_from(
  con,
  table,
  from,
  by,
  update_values,
  ...,
  returning_cols = NULL
)

sql_query_upsert(
  con,
  table,
  from,
  by,
  update_cols,
  ...,
  returning_cols = NULL,
  method = NULL
)

sql_query_delete(con, table, from, by, ..., returning_cols = NULL)

Arguments

con	Database connection.
table	Table to update. Must be a table identifier. Use a string to refer to tables in the current schema/catalog or I() to refer to tables in other schemas/catalogs.
from	Table or query that contains the new data. Either a table identifier or SQL.
insert_cols	Names of columns to insert.
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.

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.

Value

A SQL query.

Examples

sql_query_upsert(
  con = simulate_postgres(),
  table = ident("airlines"),
  from = ident("df"),
  by = "carrier",
  update_cols = "name"
)

hadley/dbplyr documentation built on April 2, 2024, 2:25 p.m.