This page View Markdown Open in ChatGPT Open in Claude

Update many¶

Makes the same change to every row that find matches, in one statement of the database. It answers how many rows changed. With returnData: true, it answers the rows too.

In short

PUT /api/schema/<user-path>/<instance>/<database>/<table>/update-many
Body : { "find": {…}, "updateData": {…} }. "find": {} changes every row.
The answer is { updatedRowsCount }.
No version check of optimistic concurrency control here.


Method	PUT
URL	`/api/schema/admin/mysql8/inventory/customers/update-many`
Body	`{ find, updateData, returnData? }`
Query params	none
Answer	`data` : `{ updatedRowsCount }`. With `returnData: true` : `{ updatedRowsCount, updatedRows }`.
Databases	MongoDB, MySQL, MariaDB, PostgreSQL, SQL Server, Oracle, TiDB, Percona
Cached	no. It clears the cached answers of the table.
From code	`g.sys.db.updateMany`
API id	`SCHEMA_UPDATE_MANY`
Without a schema	`/api/gen/admin/mysql8/inventory/customers/update-many` : below

The examples change the table of get all.

The call¶

PUT /api/schema/admin/mysql8/inventory/customers/update-many

Body

{
    "find": { "customer_id": { "$in": [ 2, 3 ] } },
    "updateData": { "last_name": "Brown" }
}

Answer

{ "success": true, "statusCode": 200, "data": { "updatedRowsCount": 2 } }

find takes every operator of find : $in, $gt, $and, $or, $like, keys with dots…
updatedRowsCount counts the rows the database really changed.

The changed rows : returnData¶

Body

{
    "find": { "customer_id": { "$in": [ 2, 3 ] } },
    "updateData": { "last_name": "Brown" },
    "returnData": true
}

Answer

{
    "success": true,
    "statusCode": 200,
    "data": {
        "updatedRowsCount": 2,
        "updatedRows": [
            { "customer_id": 2, "first_name": "Alice", "last_name": "Brown", "last_update": "2022-10-15T02:10:40.000Z", "pincode": 382346, "isActive": 1 },
            { "customer_id": 3, "first_name": "Mallory", "last_name": "Brown", "last_update": "2022-09-13T03:44:05.000Z", "pincode": 382347, "isActive": 1 }
        ]
    }
}

API Maker reads the keys of the matching rows first, then the rows after the change. So a change to the very columns find looks at still finds them.
That costs two reads more than the update. Ask for the rows only when you use them.

More filters¶

Two conditions : $and

{ "find": { "$and": [ { "isActive": 1 }, { "pincode": { "$gt": 382345 } } ] }, "updateData": { "isActive": 0 } }

A related table : the orders of one customer (orders.customer_id refers to customers)

{ "find": { "customer_id.email": "[email protected]" }, "updateData": { "status": "CANCELLED" } }

What the schema does¶

updateData is converted and checked like an update : types, conversionFun, encryption, rules. find is converted like a query.
The version check does not apply : the rows are changed in the database without being read first.
The columns of updateData must be ones the groups of the caller may write.

Good to know¶

Without returnData, no row is read back.
The cached answers of the table are cleared, and WebSocket subscribers get the news once for the call.

From your code¶

g.sys.db.updateMany makes the same call from custom APIs, hooks, events, schedulers and test cases. Add returnData: true next to find and updateData for the changed rows.

Without a schema : /api/gen¶

Every table also answers this operation under /api/gen, with or without a schema :


URL	`/api/gen/admin/mysql8/inventory/customers/update-many`
API id	`GEN_UPDATE_MANY`
From code	`g.sys.db.gen.updateManyGen`

The values go in as you send them, and find matches the type you send. On MongoDB, a text that looks like an ObjectId is turned into one. On SQL without a schema, API Maker knows no primary key : with returnData: true, updatedRows are the rows that find matches after the update.

Headers and errors¶

This operation takes the request headers of every API. It answers the envelope, with the status codes and the messages of every API.

Related : All APIs · Table schema · Pre hooks · Post hooks · API group permission