examples · sql · 11 / 13 · limited

11. UPDATE via /sql (returns translation)

caveat

UPDATE through /sql currently translates the statement into a typed payload but does not execute it. To actually mutate the row, send the translated payload to the /rows/:schema/:pk endpoint. For real production writes, skip /sql entirely - go straight to the row endpoint.

the /sql path - what happens

POST /v1/tenants/:t/sql (translation only)

# Sending the UPDATE through /sql:
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 (the translation, not an execution)

{
  "kind":   "update",
  "schema": "shop.orders",
  "rows":   [
    { "id": "o_001", "status": "shipped" }
  ]
}

The response tells you what the engine would write. To actually write it, follow with a PUT to /rows/shop.orders/o_001 with the new row body.

the recommended path - row endpoint

Sending a row write directly is one round-trip, atomic, and works with the SDK's idempotency-key plumbing.

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

# Recommended: PUT directly to the row endpoint.
# Sends the new representation; the engine overwrites the row atomically.
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"
  }'

db.rows.put("shop.orders", {
    "id":           "o_001",
    "customer_id":  "c_1",
    "amount_cents": 4990,
    "status":       "shipped",
})

common mistakes

Assuming /sql executed. The response says "kind": "update" but that's the translation. Check by re-reading the row.
Partial updates via /rows PUT. A PUT overwrites the whole row. To update one column, fetch the row, change the field, PUT the result.