Chapter 11. UPDATE resultsets


UPDATEs using joins
Updating individual rows
  <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">

  <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">
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 = key. update() 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;


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.