UPDATE
SQL statementCreates 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)
... | Column names and associated values. Each argument should be
single-valued. Use |
---|---|
.where | Conditions passed on to |
.ignore | Add |
set_vars | Named list of columns and values. Each list entry can have
only one value. Entries starting with |
`_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 |
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)"#> [1] "UPDATE t1 SET col1=\"a\", col2=42 WHERE id IN (1, 2, 3, 4, 5)"#> [1] "UPDATE iris SET some_column=1, some_other_col=\"high\" WHERE another_col=2"#> [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"#> [1] "UPDATE items , month SET items.price=month.price WHERE items.id=month.id"