Update data

Perform update operations on MySQL databases via HTTP RESTful API. For details about specific interfaces, refer to HTTP API/MySQL Database.

Basic Syntax

PATCH https://your-envId.api.tcloudbasegateway.com/v1/rdb/rest/:table
Authorization: Bearer <access_token>
Content-Type: application/json

💡 Tip: For access_token, refer to Obtain AccessToken

Basic Update

# Updating a single data record.
curl -X PATCH 'https://{{host}}/v1/rdb/rest/film?id=eq.1' \
-H 'Authorization: Bearer <access_token>' \
-H 'Content-Type: application/json' \
-d '{
  "duration": 202
}'

💡 Note: By default, update operations do not return data, only status codes and the number of affected rows.

Response

• content-range: */1 indicates the number of affected rows, meaning one data record has been updated.
• preference-applied: return=minimal indicates that the return=minimal policy was adopted for the write operation (default behavior for write operations, which does not generate a response body).

Response Example

HTTP/1.1 204 No Content
Header:
    content-range: */1
    preference-applied: return=minimal
Body: (Empty)

Update with Data Return

# Updating Data with Updated Full Record Return
curl -X PATCH 'https://{{host}}/v1/rdb/rest/film?select=*&id=in.(3,4,5)' \
-H 'Authorization: Bearer <access_token>' \
-H 'Content-Type: application/json' \
-H 'Prefer: return=representation' \
-d '{
    "director": "Francis Ford Coppola"
}'

Request

• Specify the Header Prefer: return=representation in the request to indicate that the updated data will be returned after the update operation.

💡 Note: Returning data after an update essentially involves an update + query operation, which results in two database requests. Both requests are within the same transaction, thus avoiding dirty reads. However, if the query fails, the update will also be rolled back.

Response Example

HTTP/1.1 200 OK
Header:
    content-length: 387
    content-range: */3
    content-type: application/json; charset=utf-8
    preference-applied: return=representation
Body:
[
    {
        "director": "Francis Ford Coppola",
        "duration": 175,
        "id": 3,
        "release_year": 1972,
        "title": "The Godfather"
    },
    {
        "director": "Francis Ford Coppola",
        "duration": 202,
        "id": 4,
        "release_year": 1974,
        "title": "The Godfather: Part II"
    },
    {
        "director": "Francis Ford Coppola",
        "duration": 194,
        "id": 5,
        "release_year": 1990,
        "title": "The Godfather: Part III"
    }
]

Unconditional Update Restrictions

# Unconditional Updates Will Be Rejected
curl -X PATCH 'https://{{host}}/v1/rdb/rest/film' \
-H 'Authorization: Bearer <access_token>' \
-H 'Content-Type: application/json' \
-d '{
    "duration": 202
}'

Response

• Unconditional updates are considered unsafe operations. When such requests occur, the system triggers protection mechanisms to reject them.

Response Example

HTTP/1.1 400 Bad Request
Header:
    content-length: 113
    content-type: application/json; charset=utf-8
Body: 
{
    "code": "BadApiRequest",
    "details": "",
    "hint": "",
    "message": "UPDATE requires a WHERE clause"
}

Complex Conditional Update

# Update with Complex Conditions + Sorting + Pagination
curl -X PATCH 'https://{{host}}/v1/rdb/rest/film?select=*&id=in.(3,4,5)&and=(duration.gte.150,duration.lt.200)&or=(director.eq.Christopher Nolan,director.eq.Frank Darabont)&limit=1&order=id.desc' \
-H 'Authorization: Bearer <access_token>' \
-H 'Content-Type: application/json' \
-H 'Prefer: return=representation' \
-d '{
    "director": "Francis Ford Coppola"
}'

Request

• id=in.(3,4,5): Update records where the id is among 3, 4, or 5.
• and=(duration.gte.150,duration.lt.200): and the duration is greater than or equal to 150 minutes, and less than 200 minutes
• or=(director.eq.Christopher Nolan,director.eq.Frank Darabont): or the director is Christopher Nolan or Frank Darabont
• limit=1: Update at most 1 record
• order=id.desc: Update after sorting by id in descending order

Response Example

HTTP/1.1 200 OK
Header:
    content-length: 142
    content-range: */1
    content-type: application/json; charset=utf-8
    preference-applied: return=representation
Body: 
[
    {
        "_openid": "1977683311217119233",
        "director": "Francis Ford Coppola",
        "duration": 194,
        "id": 5,
        "release_year": 1990,
        "title": "The Godfather: Part III"
    }
]

Special Scenarios

When performing an update operation and returning data, the system will obtain the updated data based on the primary key, but the following scenarios require special attention:

Single-Field Primary Key Table

If the table's primary key is a single field, and during the update the primary key serves both as the update condition and the update value, the update will succeed, but the query return may not meet expectations:

# Change the primary key of the movie information with id=1 to 2
curl -X PATCH 'https://{{host}}/v1/rdb/rest/film?select=title,release_year,duration&id=eq.1' \
-H 'Authorization: Bearer <access_token>' \
-H 'Content-Type: application/json' \
-H 'Prefer: return=representation' \
-d '{
    "id": 2
}'

At this point, the data can be updated normally. The Content-Range in the response header will indicate that 1 row is affected. However, because id serves both as the primary key and the updated value, the query will return empty results.

Composite Primary Key Table

Similar to the single-field primary key, if any field in a composite primary key serves both as the condition and the updated value during an update, the update will succeed, but the query return may not meet expectations:

# The composite primary key for table user_activities is (activity_date, activity_type)
# Update the activity_type to logout for records with activity_type as login in the user_activities table
curl -X PATCH 'https://{{host}}/v1/rdb/rest/user_activities?activity_type=eq.login' \
-H 'Authorization: Bearer <access_token>' \
-H 'Content-Type: application/json' \
-H 'Prefer: return=representation' \
-d '{
    "activity_type": "logout"
}'

At this point, the data can be updated normally. The Content-Range in the response header will indicate that 1 row is affected. However, because activity_type is part of the composite primary key and serves as the updated value, the query will return empty results.

Table Without Primary Key

If a table has no primary key, then during an update, you cannot specify the request header Prefer: return=representation. Updating tables without primary keys and returning data is not supported, and it will throw an error:

table <table_name> has no primary key, cannot use update-with-return, suggest remove query

After removing Prefer: return=representation, tables without primary keys can be updated normally.