Update one row by primary key

Change one known row through SQL. A primary key is the column, or set of columns, that identifies the row.

Before you run it: This UPDATE path targets one primary-key-pinned row. For several rows, read their keys and update each row separately.

Load the introductory orders sample dataset first. It contains o_001, o_002 and o_003 with customer_id, amount_cents and status. Use a disposable copy because this recipe changes those rows.

Change one order selected by its primary keyThis example updates a primary-key match; it does not demonstrate a non-key bulk update.

Load the test orderConfirm that o_001 is the intended record.
Set the new statusUse its primary key in WHERE and set status to shipped.
Read back the orderCheck rows_affected and the stored status.

Follow the example

Find the row’s primary key.
Put that key in the UPDATE filter.
Check rows_affected.
Read the row to confirm the change.

Supported operations

UPDATE through /sql executes against the engine and returns rows_affected. (Earlier builds only translated the statement - that's no longer the case.)

Run the SQL update

# UPDATE through /sql executes against the engine.
curl -X POST "https://$OC_HOST/v1/tenants/$OC_TENANT/sql" \
  -H "Authorization: Bearer $OC_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{
    "sql": "UPDATE shop.orders SET status = '\''shipped'\'' WHERE id = '\''o_001'\''"
  }'

Check the response

{
  "kind":          "update",
  "schema":        "shop.orders",
  "rows_affected": 1
}

The matched row is mutated in place. rows_affected is 0 when the WHERE matches nothing.

Update one key at a time

UPDATE via /sql targets one row identified by its primary key - the WHERE must pin the PK (WHERE id = '...'). Set-based updates that match on a non-PK column (e.g. UPDATE shop.orders SET status = 'shipped' WHERE customer_id = 'c_1') are not applied as a bulk mutation. To update many rows, read the matching primary keys first, then issue one PK-pinned UPDATE - or one PUT to the row endpoint - per key.

Replace the row through the row API

When you already have the complete new row, a PUT to the row endpoint overwrites it atomically in one round-trip and carries the SDK's idempotency-key plumbing.

# Alternative: PUT the full row representation directly.
# Use this when you want to overwrite the whole row in one shot.
curl -X PUT "https://$OC_HOST/v1/tenants/$OC_TENANT/rows/shop.orders/o_001" \
  -H "Authorization: Bearer $OC_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{
    "id":           "o_001",
    "customer_id":  "c_1",
    "amount_cents": 4990,
    "status":       "shipped"
  }'

Expected result

No matching row produces rows_affected: 0.

Continue

Continue to remove rows deliberately.

Update one row by primary key