Skip to main content
Skip to main content

UPDATE

UPDATE​

Name​

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

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'
  • where condition: the condition that is expected to be updated, an expression that returns true or false can be

Optional Parameters​

  • cte: Common Table Expression, e.g. '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.

Note​

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

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;
  1. Increment the v1 column of the k1=1 column in the 'test' table by 1
UPDATE test SET v1 = v1+1 WHERE k1=1;
  1. 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 |
+----+----+----+--------+------------+
  1. 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;

Keywords​

UPDATE

Best Practice​