UPDATE resultsets
- Schema:
<schema> <table name="accounts"> <column name="account_id" datatype="int64_t" primarykey='1' /> <column name="account_type_id" datatype="int64_t" /> <column name="code" /> <join class="ledger_entries"> <column>account_id</column> </join> </table> <table name="ledger_entries"> <column name="ledger_entry_id" datatype="int64_t" primarykey='1' /> <column name="account_id" datatype="int64_t"/> <column name="ledger_date" datatype="x::ymd"/> <column name="amount" datatype="double"/> <join class="accounts" type="inner join" only="1"> <column>account_id</column> </join> </table> </schema>- Code:
int64_t key; // ... auto accounts_rs=accounts::create(conn); accounts->search("account_id", "=", key); size_t n=accounts->update("account_type_id", 2, "code", nullptr);
update() takes a variable list of name and value
tuples and creates an SQL UPDATE statement out of
the resultset.
For each value, update() takes any datatype that
execute()
understands.
nullptr specifies a
NULL valus. This example executes SQL
that's equivalent to:
“UPDATE accounts SET
account_type_id=2, code=NULL WHERE
account_id = ”.
keyupdate() also takes a
std::map in place of a name and value tuple,
which gets interpreted as a list of name/value tuples:
std::map<std::string, int> values; values["account_type_id"]=2; size_t n=accounts->update(values, "code", nullptr);
This example also executes the same UPDATE,
“account_type_id=2, code=NULL”.
update() returns the number of updated rows,
with the semantics defined by the database drivers. Some database drivers
do not count the rows whose columns already had the same values that
update() set, others count all rows
selected by the resultset's constraint.
update() throws an exception if the resultset
has a
group_by(),
having(), or an
order_by().
UPDATEs using joins
It's generally possible to update() a resultset
with joins. The problem is that the SQL syntax for update with joins
varies, depending on the database driver. Currently,
LIBCXX SQL Library handles it only with MySQL and
Postgres drivers. The first update() for a
resultset with join runs a heuristic check on the database driver, and
selects the appropriate syntax for an SQL UPDATE
with joins.
auto accounts_rs=accounts::create(conn); auto join=accounts_rs->join_ledger_entries(); accounts_rs->search(join->get_table_alias() + ".amount", "=", 0); size_t n=accounts_rs->update("account_type_id", "1");
This example executes SQL equivalent to either:
“UPDATE accounts AS accounts
LEFT JOIN ledger_entries AS
ledger_entries ON accounts.account_id=ledger_entries.account_id
SET account_type_id=? WHERE (ledger_entries.amount =
0)”, or
“UPDATE accounts AS
updated_table SET account_type_id=? FROM
(SELECT accounts.account_id FROM accounts AS accounts LEFT JOIN
ledger_entries AS ledger_entries ON
accounts.account_id=ledger_entries.account_id
WHERE (ledger_entries.amount = 0)) AS joins WHERE
updated_table.account_id=joins.account_id”
In the latter case, the schema must accuratively specify the table's primary key columns, for the query to work correctly.