UPDATE

Description

This statement is used to update the data. The UPDATE statement currently only supports the UNIQUE KEY model.

The UPDATE operation currently only supports updating the Value column. The update of the Key column can refer to Using FlinkCDC to update Key column.

Syntax

[cte]
UPDATE target_table [table_alias]
    SET assignment_list
    [ FROM additional_tables]
    [WHERE condition]
    [ORDER BY column [ASC | DESC] [NULLS FIRST | NULLS LAST] [, ...]]
    [LIMIT [offset,] count]

Required Parameters

• target_table: The target table of the data to be updated. Can be of the form 'db_name.table_name'
• assignment_list: The target column to be updated, in the format 'col_name = value, col_name = value'

Optional Parameters

• cte: Common Table Expression, eg 'WITH a AS SELECT * FROM tbl'
• table_alias: alias of table
• FROM additional_tables: Specifies one or more tables to use for selecting rows to update or for setting new values. Note that if you want use target table here, you should give it a alias explicitly.
• WHERE condition: The condition that is expected to be updated, an expression that returns true or false.
• ORDER BY column: Specifies the order in which rows are updated. Typically used together with LIMIT to control which rows are affected.
• LIMIT [offset,] count: Limits the number of rows to be updated. When used with ORDER BY, updates the first count rows after sorting. If offset is specified, skips the first offset rows before updating. If used without ORDER BY, the set of affected rows is non-deterministic.

tip

ORDER BY and LIMIT in UPDATE statements are supported since version 4.1.0.

Note

The current UPDATE statement only supports row updates on the UNIQUE KEY model.

When neither WHERE nor LIMIT is specified, all rows in the table will be updated. Always verify the intended scope before omitting the WHERE clause.

Example

The test table is a unique model table, which contains four columns: k1, k2, v1, v2. Where k1, k2 are keys, v1, v2 are values, and the aggregation method is Replace.

1. Update the v1 column in the 'test' table that satisfies the conditions k1 =1 , k2 =2 to 1

UPDATE test SET v1 = 1 WHERE k1=1 and k2=2;

2. Increment the v1 column of the k1=1 column in the 'test' table by 1

UPDATE test SET v1 = v1+1 WHERE k1=1;

3. use the result of t2 join t3 to update t1

-- create t1, t2, t3 tables
CREATE TABLE t1
  (id INT, c1 BIGINT, c2 STRING, c3 DOUBLE, c4 DATE)
UNIQUE KEY (id)
DISTRIBUTED BY HASH (id)
PROPERTIES('replication_num'='1', "function_column.sequence_col" = "c4");

CREATE TABLE t2
  (id INT, c1 BIGINT, c2 STRING, c3 DOUBLE, c4 DATE)
DISTRIBUTED BY HASH (id)
PROPERTIES('replication_num'='1');

CREATE TABLE t3
  (id INT)
DISTRIBUTED BY HASH (id)
PROPERTIES('replication_num'='1');

-- insert data
INSERT INTO t1 VALUES
  (1, 1, '1', 1.0, '2000-01-01'),
  (2, 2, '2', 2.0, '2000-01-02'),
  (3, 3, '3', 3.0, '2000-01-03');

INSERT INTO t2 VALUES
  (1, 10, '10', 10.0, '2000-01-10'),
  (2, 20, '20', 20.0, '2000-01-20'),
  (3, 30, '30', 30.0, '2000-01-30'),
  (4, 4, '4', 4.0, '2000-01-04'),
  (5, 5, '5', 5.0, '2000-01-05');

INSERT INTO t3 VALUES
  (1),
  (4),
  (5);

-- update t1
UPDATE t1
  SET t1.c1 = t2.c1, t1.c3 = t2.c3 * 100
  FROM t2 INNER JOIN t3 ON t2.id = t3.id
  WHERE t1.id = t2.id;

the expect result is only update the row where id = 1 in table t1

+----+----+----+--------+------------+
| id | c1 | c2 | c3     | c4         |
+----+----+----+--------+------------+
| 1  | 10 | 1  | 1000.0 | 2000-01-01 |
| 2  | 2  | 2  |    2.0 | 2000-01-02 |
| 3  | 3  | 3  |    3.0 | 2000-01-03 |
+----+----+----+--------+------------+

4. using cte

create table orders(
    o_orderkey bigint,
    o_totalprice decimal(15, 2)
) unique key(o_orderkey)
distributed by hash(o_orderkey) buckets 1 
properties (
    "replication_num" = "1"
);

insert into orders values
(1, 34.1),
(2, 432.8);

create table lineitem(
    l_linenumber int,
    o_orderkey bigint,
    l_discount  decimal(15, 2)
) unique key(l_linenumber)
distributed by hash(l_linenumber) buckets 1 
properties (
    "replication_num" = "1"
);

insert into lineitem values
(1, 1, 1.23),
(2, 1, 3.21),
(3, 2, 18.08),
(4, 2, 23.48);

with discount_orders as (
    select * from orders 
    where o_totalprice > 100
)
update lineitem  set l_discount = l_discount*0.9
from discount_orders 
where lineitem.o_orderkey = discount_orders.o_orderkey;

5. Update with ORDER BY and LIMIT — update the v1 column to 0 for the first 3 rows with the largest v1 values where k1 > 0

UPDATE test SET v1 = 0 WHERE k1 > 0 ORDER BY v1 DESC LIMIT 3;

6. Update with ORDER BY, LIMIT and offset — skip the first 10 rows and update the next 5 rows ordered by k1

UPDATE test SET v1 = 100 ORDER BY k1 ASC LIMIT 10, 5;

Keywords

UPDATE

Best Practice