examples · sql · 11 / 13 · works today

11. UPDATE via /sql

works today

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

the /sql path

POST /v1/tenants/:t/sql

# 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'\''"
  }'

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.

scope - single row by primary key

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.

alternative - full-row PUT

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.

PUT /v1/tenants/:t/rows/:schema/:pk

# 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"
  }'