Updated on 2025-07-22 GMT+08:00

UPDATE

Function

UPDATE updates data in a table. UPDATE changes the values of the specified columns in all rows that satisfy the condition. The WHERE clause clarifies conditions. The columns to be modified need be mentioned in the SET clause; columns not explicitly modified retain their previous values.

Precautions

  • For how to use UPDATE, see UPDATE and DELETE Operations.
  • Permission requirements: To modify a table, you must have the UPDATE permission on the table and the SELECT permission on any table involved in the expression or condition.
  • The distribution column of a table cannot be modified.
  • You are not advised to create a table that needs to be frequently updated as a replication table.
  • Constraints on column-store tables:
    • Column-store tables do not support the RETURNING clause.
    • Column-store tables do not support non-deterministic update. If you update data in one row with multiple rows of data in a column-store table, an error is reported.
    • Memory space that records update operations in column-store tables is not reclaimed. You need to clean it by executing VACUUM FULL table_name.
    • Column-store tables support lightweight UPDATE operations, which only rewrite the updated columns to reduce space usage. To enable column-store lightweight UPDATE, configure enable_light_colupdate. It is disabled by default.
    • Column-store lightweight UPDATE is unavailable and automatically changes to the regular UPDATE operation in the following scenarios: updating an index column, updating a primary key column, updating a partition column, updating a PCK column, and online scaling.
    • There is a low probability that an error is reported when column-store lightweight UPDATE and backend column-store AUTOVACUUM coexist. To avoid such an issue, you can run ALTER TABLE to set the table-level parameter enable_column_autovacuum_garbage to off (off means that the backend column-store AUTOVACUUM of tables is disabled.

Syntax

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
    UPDATE [/*+ plan_hint */] [ ONLY ] table_name [ * ] [ [ AS ] alias ]
    [ PARTITION ( partition_name ) | PARTITION FOR ( partition_key_value [, ...] ) ]
SET {column_name = { expression | DEFAULT } 
    |( column_name [, ...] ) = {( { expression | DEFAULT } [, ...] ) |sub_query }}[, ...]
    [ FROM from_list] [ WHERE condition ]
    [ RETURNING {* 
                | {output_expression [ [ AS ] output_name ]} [, ...] }];

where sub_query can be:
SELECT [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ]
{ * | {expression [ [ AS ] output_name ]} [, ...] }
[ FROM from_item [, ...] ]
[ WHERE condition ]
[ GROUP BY grouping_element [, ...] ]
[ HAVING condition [, ...] ]

Parameter Description

Table 1 UPDATE parameters

Parameter

Description

Example

plan_hint clause

Following the keyword in the /*+ */ format, hints are used to optimize the plan generated by a specified statement block. For details, see Hint-based Tuning.

-

table_name

Specifies the name (optionally schema-qualified) of the table to be updated.

test.table1 (test is the schema name).

[AS] alias

Specifies a short alias for the table for easy reference. Its value is a string, which must comply with the naming convention. For details, see Identifier Naming Conventions.

Update the employees table with alias e:

1
UPDATE employees AS e SET e.salary = e.salary * 1.1 WHERE e.department = 'technology';

partition_name

Specifies the partition name. If this parameter is specified, the data corresponding to the partition name will be updated. It is available for range partitioned tables, list partitioned tables, and other named partitioned tables. Only clusters of version 8.2.1 or later support this option.

Update data in the partition p1:

1
UPDATE test_range_row PARTITION(p1) SET d = 41;

partition_key_value

Specifies the partition key value. If this parameter is specified, the data corresponding to the partition key value will be updated

The value specified by PARTITION FOR (partition_key_value [, ...]) can uniquely identify a partition.

Update the value of b to 20 for the partition with key 41:

1
UPDATE test_range_row PARTITION FOR (41) SET b = 20;

column_name

Renames a column.

You can refer to this column by specifying the table name and column name of the target table. Example:

1
UPDATE foo SET foo.col_name = 'GaussDB';

You can refer to this column by specifying the target table alias and the column name. Example:

1
UPDATE foo AS f SET f.col_name = 'GaussDB';

-

expression

An expression or value to assign to the column.

Discount the product price by 20%:

1
UPDATE products SET     price = price * 0.8 WHERE product_id = 101;

DEFAULT

Sets the column to its default value. The value is NULL if no specified default value has been assigned to it.

Reset the status and login times of inactive users:

1
UPDATE users SET     status = DEFAULT,         login_count = DEFAULT  WHERE last_login < CURRENT_DATE - INTERVAL '30 days';

sub_query

Specifies the subquery, which is used to update the target table based on the query results of other tables in the same database.

For details about clauses in the SELECT statement, see SELECT.

Update the average salary of a department using a subquery.

1
UPDATE departments d SET avg_salary = ( SELECT AVG(salary) FROM employees e WHERE e.dept_id = d.dept_id);

from_list

Specifies a table expression, that allows the columns from other tables to appear in the WHERE condition and update the target table. This is similar to the list of tables that can be specified in the FROM clause of a SELECT statement.

NOTICE:

Note that the target table must not appear in the from_list, unless you intend a self-join (in which case it must appear with an alias in the from_list).

Example: Use the FROM from_list Clause to Preferentially Process Orders of Customers with High VIP Levels

condition

Specifies an expression that follows WHERE and returns a result of the boolean type. Only rows for which this expression returns true are updated.

Update the values of the students whose math scores are lower than 80:

1
UPDATE student_scores SET math_score = math_score + 5,    update_time = NOW() WHERE math_score < 80;

output_expression

Specifies an expression to be computed and returned by the UPDATE command after each row is updated.

Value range: The expression can use any column names of the table named by table_name or tables listed in FROM. The asterisk (*) indicates that all columns are returned.

Update and return the stock information before and after the update:

1
UPDATE inventory SET stock = stock - 10 WHERE category = 'Electronics' RETURNING   item_id AS "Product ID",    name AS "Product name",    stock AS "New stock",    stock + 10 AS "Old stock";

output_name

Specifies the name of the column returned by RETURNING to optimize the output. Its value is a string, which must comply with the naming convention. For details, see Identifier Naming Conventions.

-

Examples: Common UPDATE Scenarios

Create a sample table and insert data into it.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
DROP TABLE IF EXISTS products;
CREATE TABLE products (
    product_id INT PRIMARY KEY,         --Product ID
    name VARCHAR(100),                  --Product name
    price DECIMAL(10,2),                --Product price
    stock INT,                          --Product stock
    last_updated TIMESTAMP              -Data update time
);

INSERT INTO products VALUES
(101, 'Phone', 2999.00, 50, NULL),
(102, 'PC', 5999.00, 30, NULL),
(103, 'PAD', 1999.00, 20, NULL);

Increase the price of all products by 20% (using a statement without WHERE to update the values of all records).

1
2
UPDATE products SET price = price * 1.2 , last_updated = NOW();
SELECT * FROM products ORDER BY 1;

Increase the price of the PAD by 500 (using a statement with WHERE to update the specified data).

1
2
3
UPDATE products SET price = price + 500 , last_updated = NOW() 
WHERE name = 'PAD';
SELECT * FROM products ORDER BY 1;

Discount the price of products whose stock is greater than 40 by 20% (using a statement with WHERE to update data in a specified range).

1
2
3
UPDATE products SET price = price * 0.8 , last_updated = NOW() 
WHERE stock > 40;
SELECT * FROM products ORDER BY 1;

Adjust the price and stock of the PC (Specifying multiple values in SET)

1
2
3
UPDATE products SET price = price - 1000 , stock = stock - 10,  last_updated = NOW() 
WHERE name = 'PC';
SELECT * FROM products ORDER BY 1;

Example: Update a Specified Partition of a Partitioned Table

Create a sample table and import data into it.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
DROP TABLE IF EXISTS test_range_row;
CREATE TABLE test_range_row(a int, b int,d int)
DISTRIBUTE BY hash(a) PARTITION BY RANGE(d)
(
    PARTITION p1 values LESS THAN (60),
    PARTITION p2 values LESS THAN (75),
    PARTITION p3 values LESS THAN (90),
    PARTITION p4 VALUES LESS THAN (maxvalue)
);

INSERT INTO test_range_row PARTITION(p1) VALUES(55,20,51);
INSERT INTO test_range_row PARTITION(p1) VALUES(54,18,59);
INSERT INTO test_range_row PARTITION(p2) VALUES(15,18,63);
INSERT INTO test_range_row PARTITION(p3) VALUES(12,21,80);
INSERT INTO test_range_row PARTITION(p3) VALUES(83,21,85);
INSERT INTO test_range_row PARTITION(p4) VALUES(36,21,97);

SELECT * FROM test_range_row ORDER BY d;

Change the values of column d to 41 in partition p1.

1
2
UPDATE test_range_row PARTITION(p1) SET d = 41;
SELECT * FROM test_range_row ORDER BY d;

According to the preceding result, the partition is p1, where the values of column d are less than 60. The values of column d in partition p1 are changed to 41.

Update the values of column b to 20 in the partition where the partition key value is 41.

1
2
UPDATE test_range_row PARTITION FOR (41) SET b = 20;
SELECT * FROM test_range_row ORDER BY d;

Example: Use the FROM from_list Clause to Preferentially Process Orders of Customers with High VIP Levels

The target table needs to be updated. The orders table needs to be updated, but the FROM clause needs to be used to associate the customer ID field in the customers table to obtain customer VIP levels.

Create the customer information table and order table, which contain the following fields:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
DROP TABLE IF EXISTS customers;            
CREATE TABLE customers (                 --Customer information table to be created     
    customer_id INT PRIMARY KEY,         -- Customer ID
    name VARCHAR(100),                   --Customer name    
    vip_level INT                        --VIP level   
);
DROP TABLE IF EXISTS orders;             
CREATE TABLE orders (                    --Order table to be created
    order_id INT PRIMARY KEY,            --Order ID
    customer_id INT ,                    --Customer ID
    amount DECIMAL(10,2),                --Consumption amount
    status VARCHAR(20)                   --Order status
);

Insert test data and ensure the order status of customers is pending (It means that the orders of the three customers are not processed.)

1
2
3
4
5
6
7
8
9
INSERT INTO customers VALUES
(1,'Tom', 1)
(2,'Jack', 3),
(3,'Tony', 2);
INSERT INTO orders VALUES
(1001, 1, 500.00, 'pending'),
(1002, 2, 1200.00, 'pending'),
(1003, 3, 800.00, 'pending');
SELECT * FROM orders;

Orders of customers with high VIP levels need be prioritized when there are too many orders. FROM from_list is used to associate the customer ID in the customer table to obtain the customer VIP level. In this example, orders of customers with VIP level 3 or higher need to be processed first.

1
2
3
4
5
6
UPDATE orders o
SET status = 'priority'      --Set the order status to priority.
FROM customers c
WHERE o.customer_id = c.customer_id AND c.vip_level >= 3;  --Obtain the customers whose VIP levels are higher than 3 from the customer table.
SELECT * FROM orders;
     

Example: Use DEFAULT to Reset the Status of an Inactive User

Create a user table (with DEFAULT) and insert data.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
DROP TABLE IF EXISTS users;
CREATE TABLE users (    
user_id INT PRIMARY KEY,                  --User ID
username VARCHAR(50) ,                    --Username
login_count INT DEFAULT 0,                -- Number of login times. The default value is 0.
last_login TIMESTAMP,                     --Last login time
status VARCHAR(20) DEFAULT 'active');     --User status. The default value is active.

INSERT INTO users (user_id, username, login_count, last_login) VALUES
(1, 'user1', 5, '2023-06-01 10:00:00'),
(2, 'user2', 3, '2023-06-15 14:30:00');
SELECT * FROM users;

Reset the status and login times of inactive users:

1
2
3
4
5
UPDATE users SET 
status = DEFAULT,      -- Reset the status to active    
login_count = DEFAULT  -- Reset the login times to 0
WHERE last_login < CURRENT_DATE - INTERVAL '30 days';
SELECT * FROM users;