@Rocky best way to update a column value that is part of the primary key using column only mode?

In StarRocks, it is not possible to directly update a column that is part of the Primary Key. The primary key columns are used to identify the row's location in the storage engine, and changing them would change the identity of the row. According to the official documentation, "For data consistency purposes, the primary key values cannot be updated." The Best Way to "Update" a Primary Key Column Since a direct

UPDATE

is not supported for PK columns, you must perform a Delete + Insert operation. Depending on your scenario, here are the best practices: 1. Using a Load Job (Recommended for Batch Updates) If you are updating a large volume of data (where you might have been considering "column only mode"), the most efficient way is to use a single load job (Stream Load or Broker Load) that includes both the deletion of the old record and the insertion of the new one using the

__op

field. * Step 1: Prepare your data with two entries for every "update": * One entry with the old primary key and

__op = 1

(Delete). * One entry with the new primary key and

__op = 0

(Upsert/Insert). * Step 2: Execute the load. StarRocks handles these as a single transaction within the batch. 2. Using SQL DML (For Small/Ad-hoc Changes) If you are working directly in SQL, you must use two statements:

sql
-- 1. Delete the old row
DELETE FROM your_table WHERE pk_col1 = 'old_value';

-- 2. Insert the new row
INSERT INTO your_table (pk_col1, other_cols...) VALUES ('new_value', ...);

Important Clarification on "Column Only Mode" The Column Only Mode (

partial_update_mode = 'column'

), introduced in StarRocks v3.1, is a performance optimization for Partial Updates of non-primary key columns. * It is designed for scenarios where you update a small number of columns across a large number of rows. * It cannot be used to update the Primary Key itself, as the Primary Key is the "lookup key" used to find which row to update. Summary of Restrictions Operation | Primary Key Columns | Non-Primary Key Columns Direct UPDATE | ❌ Not Supported | ✅ Supported Partial Update (Row Mode) | ❌ Not Supported | ✅ Supported Partial Update (Column Mode) | ❌ Not Supported | ✅ Supported (v3.1+) Delete + Insert | ✅ Recommended | ✅ Supported If your use case requires frequent updates to a column, you should reconsider whether that column should be part of the Primary Key definition or if it should be a regular "value" column instead. References • faq/Others.md • sql-reference/sql-statements/table_bucket_part_index/UPDATE.md • table_design/table_types/primary_key_table.md • loading/Load_to_Primary_Key_tables.md