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.

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')
#> [1] "UPDATE t1 SET col1=\"a\""
UPDATE('t1', col1 = ~col2 * 1.25)
#> [1] "UPDATE t1 SET col1=col2 * 1.25"
UPDATE('t1', col1 = 'a', col2 = 42, .where = 'id' %IN% 1:5)
#> [1] "UPDATE t1 SET col1=\"a\", col2=42 WHERE id IN (1, 2, 3, 4, 5)"
UPDATE('t1', col1 = 'a', col2 = 42) %+% WHERE('id' %IN% 1:5)
#> [1] "UPDATE t1 SET col1=\"a\", col2=42 WHERE id IN (1, 2, 3, 4, 5)"
UPDATE('iris', some_column = 1, some_other_col = "high") %+% WHERE(eq(another_col = 2))
#> [1] "UPDATE iris SET some_column=1, some_other_col=\"high\" WHERE another_col=2"
UPDATE('t', id = ~id + 1) %+% ORDER_BY(DESC('id'))
#> [1] "UPDATE t SET id=id + 1 ORDER BY id DESC"
# Multiple tables UPDATE(c('items', 'month'), items.price = ~month.price, .where = eq(items.id = ~month.id))
#> [1] "UPDATE items , month SET items.price=month.price WHERE items.id=month.id"
UPDATE(c('items', 'month'), items.price = ~month.price) %+% WHERE(eq(items.id = ~month.id))
#> [1] "UPDATE items , month SET items.price=month.price WHERE items.id=month.id"