UPDATE

Function Description

Updates data in a table. The UPDATE statement modifies specified columns in all rows that meet the declared condition, with the condition set via the WHERE clause. Columns listed in the SET clause are updated, while unlisted columns retain their original values.

Notes

This section only covers syntax added by the Dolphin plugin; the original openGauss UPDATE syntax remains unchanged. Refer to the official openGauss documentation for the base UPDATE syntax.

Syntax Formats

Single-Table Update

[ WITH [ RECURSIVE ] cte_expression [, ...] ]
UPDATE [/*+ execution_plan_hint */] [IGNORE] [ (ONLY) ] target_table [ partition_spec ] [ * ] [ [ AS ] table_alias ]
SET {column_name = { value_expr | DEFAULT }
    |( column_name [, ...] ) = {( { value_expr | DEFAULT } [, ...] ) | nested_select }}[, ...]
    [ FROM additional_tables] [ WHERE filter_condition ]
    [ ORDER BY {sort_expr [ [ ASC | DESC | USING custom_operator ] [ NULLS { FIRST | LAST } ] ]} [, ...] ]
    [ LIMIT { row_count | ALL } ]
    [ RETURNING {*
                | {output_expr [ [ AS ] output_alias ]} [, ...] }];

Multi-Table Update

[ WITH [ RECURSIVE ] cte_expression [, ...] ]
UPDATE [/*+ execution_plan_hint */] [IGNORE] target_table_list
SET {column_name = { value_expr | DEFAULT }
    |( column_name [, ...] ) = {( { value_expr | DEFAULT } [, ...] ) | nested_select }}[, ...]
    [ FROM additional_tables] [ WHERE filter_condition ];

where nested_select is a standard SELECT query:
SELECT [ ALL | DISTINCT [ ON ( distinct_expr [, ...] ) ] ]
{ * | {select_expr [ [ AS ] col_alias ]} [, ...] }
[ FROM data_source [, ...] ]
[ WHERE filter_condition ]
[ GROUP BY grouping_expr [, ...] ]
[ HAVING having_condition [, ...] ]
[ ORDER BY {sort_expr [ [ ASC | DESC | USING custom_operator ] | nlssort_expr ] [ NULLS { FIRST | LAST } ]} [, ...] ]
[ LIMIT { [skip_rows,] row_count | ALL } ]

Parameter Description

• IGNORE When an UPDATE statement with the IGNORE keyword encounters an error in specified scenarios, it downgrades the error to a warning and continues executing, leaving other unaffected operations intact. Eligible error scenarios are:
  1. Violation of NOT NULL constraint: If a statement violates a table's NOT NULL constraint, this keyword triggers the configured GUC parameter sql_ignore_strategy to handle it:
     • ignore_null: Skip the UPDATE for violating rows and proceed.
     • overwrite_null: Replace the violating NULL with the target column type's default value and proceed.

[!NOTE] The sql_ignore_strategy GUC parameter is an enum with valid values: ignore_null, overwrite_null.

2. Violation of UNIQUE constraint: Skip the UPDATE for rows that violate the unique constraint and continue.
3. Partitioned table row mismatch: Skip rows that cannot be mapped to any valid partition during a partitioned table UPDATE and continue.
4. Type conversion failure: Handle mismatched new values and target column types with the following strategies:
   • Both numeric types: Use the value direct if within range; otherwise, clamp to the column type's max/min.
   • Both string types: Use the value directly if within length limit; otherwise, truncate to the first N allowed characters.
   • Incompatible types: Update to the target column type's default value.

The IGNORE keyword does not support column-store tables.

Examples

IGNORE Keyword Examples

First, create a B-compatible database:

CREATE DATABASE dolphin_test_db DBCOMPATIBILITY 'B';
\c dolphin_test_db

Ignoring NOT NULL Constraint Violations

-- Create test table
dolphin_test_db=# CREATE TABLE product_inventory (product_id INT NOT NULL, stock INT);
CREATE TABLE

-- Insert initial data
dolphin_test_db=# INSERT INTO product_inventory VALUES (101, 50), (102, 30);
INSERT 0 2
dolphin_test_db=# SELECT * FROM product_inventory;
 product_id | stock
------------+-------
        101 |    50
        102 |    30
(2 rows)

-- Strategy 1: Skip violating rows
dolphin_test_db=# SET sql_ignore_strategy = 'ignore_null';
SET
dolphin_test_db=# UPDATE /*+ ignore_error */ product_inventory SET product_id = NULL WHERE stock < 40;
WARNING:  null value in column "product_id" violates not-null constraint
DETAIL:  Failing row contains (null, 30).
UPDATE 0
dolphin_test_db=# SELECT * FROM product_inventory;
 product_id | stock
------------+-------
        101 |    50
        102 |    30
(2 rows)

-- Strategy 2: Overwrite with default (0 for INT)
dolphin_test_db=# SET sql_ignore_strategy = 'overwrite_null';
SET
dolphin_test_db=# UPDATE /*+ ignore_error */ product_inventory SET product_id = NULL WHERE stock < 40;
WARNING:  null value in column "product_id" violates not-null constraint
DETAIL:  Failing row contains (null, 30).
UPDATE 1
dolphin_test_db=# SELECT * FROM product_inventory;
 product_id | stock
------------+-------
        101 |    50
          0 |    30
(2 rows)

Ignoring UNIQUE Constraint Violations

-- Create test table
dolphin_test_db=# CREATE TABLE category_tags (tag_id INT UNIQUE, tag_name VARCHAR(30));
NOTICE:  CREATE TABLE / UNIQUE will create implicit index "category_tags_tag_id_key" for table "category_tags"
CREATE TABLE

-- Insert initial data
dolphin_test_db=# INSERT INTO category_tags VALUES (1, 'Electronics'), (2, 'Books');
INSERT 0 2

-- Attempt duplicate update
dolphin_test_db=# UPDATE /*+ ignore_error */ category_tags SET tag_id = 1 WHERE tag_name = 'Books';
WARNING:  duplicate key value violates unique constraint in table "category_tags"
UPDATE 0
dolphin_test_db=# SELECT * FROM category_tags;
 tag_id |  tag_name
--------+------------
      1 | Electronics
      2 | Books
(2 rows)

Ignoring Partitioned Table Row Mismatches

-- Create range-partitioned test table
dolphin_test_db=# CREATE TABLE quarterly_sales
(
    sale_id INT NOT NULL,
    amount NUMERIC(10,2),
    quarter INT NOT NULL
) WITH(segment = on) PARTITION BY RANGE (quarter)
(
    PARTITION q1 VALUES LESS THAN(4),
    PARTITION q2 VALUES LESS THAN(7),
    PARTITION q3 VALUES LESS THAN(10),
    PARTITION q4 VALUES LESS THAN(13)
);
CREATE TABLE

-- Insert initial data
dolphin_test_db=# INSERT INTO quarterly_sales VALUES (1, 1500.75, 2);
INSERT 0 1

-- Attempt invalid quarter update
dolphin_test_db=# UPDATE /*+ ignore_error */ quarterly_sales SET quarter = 15 WHERE sale_id = 1;
WARNING:  fail to update partitioned table "quarterly_sales".new tuple does not map to any table partition.
UPDATE 0
dolphin_test_db=# SELECT * FROM quarterly_sales;
 sale_id | amount  | quarter
---------+---------+---------
       1 | 1500.75 |       2
(1 row)

Handling Type Conversion Failures

-- Numeric type clamping
dolphin_test_db=# CREATE TABLE temperature_logs (reading_id INT, temp_c SMALLINT);
CREATE TABLE
dolphin_test_db=# INSERT INTO temperature_logs VALUES (1, 25);
INSERT 0 1
dolphin_test_db=# UPDATE /*+ ignore_error */ temperature_logs SET temp_c = 400 WHERE reading_id = 1;
WARNING:  smallint out of range
CONTEXT:  referenced column: temp_c
UPDATE 1
dolphin_test_db=# SELECT * FROM temperature_logs;
 reading_id | temp_c
------------+--------
          1 |    32767
(1 row)

-- String type truncation
dolphin_test_db=# CREATE TABLE short_messages (msg_id INT, content VARCHAR(6));
CREATE TABLE
dolphin_test_db=# INSERT INTO short_messages VALUES (1, 'Hi');
INSERT 0 1
dolphin_test_db=# UPDATE /*+ ignore_error */ short_messages SET content = 'HelloWorld' WHERE msg_id = 1;
WARNING:  value too long for type character varying(6)
CONTEXT:  referenced column: content
UPDATE 1
dolphin_test_db=# SELECT * FROM short_messages;
 msg_id | content
--------+---------
      1 | HelloW
(1 row)