UPDATE: Create 'UPDATE' SQL statement

In gadenbuie/sqrrrl: Utility Functions For Building SQL Queries

Description

Creates an UPDATE SQL statement for single or multiple tables. The first argument is always the table name(s), where a named vector can be used to set table aliases. Additional arguments must be named and are parsed into the SET SQL statement. Argument names reference columns and the assigned values are used as values in the SET statement and will be quoted – for example a = 'b' returns 'SET a="b"'. To escape quoting, use ~, and anything after the ~ will be added directly to the statement – a = ~b returns a=b. WHERE conditions can be added via the optional .where argument, but it is easier to simply add the where clause after the UPDATE with WHERE. The same applies for ORDER_BY and LIMIT conditions.

Usage

UPDATE(`_tables`, ..., .where = NULL, .ignore = FALSE)

UPDATE_(`_tables`, set_vars, .where = NULL, .ignore = FALSE)

Arguments

...	Column names and associated values. Each argument should be single-valued. Use ~ to escape quoting: anything after the ~ character will be added literally as written to the statment. This can be used to escape SQL commands.
.where	Conditions passed on to WHERE clause (optional)
.ignore	Add IGNORE keyword to UPDATE clause
set_vars	Named list of columns and values. Each list entry can have only one value. Entries starting with ~ will be escaped from quoting.
'_tables'	Vector of table name(s) for update (vector names are used as aliases). Should be specified as the first entry, the odd parameter name is used to avoid collisions with column names specified in ....

Examples

UPDATE('t1', col1 = 'a')
UPDATE('t1', col1 = ~col2 * 1.25)
UPDATE('t1', col1 = 'a', col2 = 42, .where = 'id' %IN% 1:5)
UPDATE('t1', col1 = 'a', col2 = 42) %+% WHERE('id' %IN% 1:5)
UPDATE('iris', some_column = 1, some_other_col = "high") %+% WHERE(eq(another_col = 2))
UPDATE('t', id = ~id + 1) %+% ORDER_BY(DESC('id'))

# Multiple tables
UPDATE(c('items', 'month'), items.price = ~month.price, .where = eq(items.id = ~month.id))
UPDATE(c('items', 'month'), items.price = ~month.price) %+% WHERE(eq(items.id = ~month.id))

gadenbuie/sqrrrl documentation built on May 24, 2019, 4:04 a.m.