如何使用UPDATE将表中对应列的数据更新

示例1：创建非分区表acid_update，并导入数据，执行UPDATE操作更新满足指定条件的行对应的列数据。命令示例如下：

--创建Transactional表acid_update。

CREATE TABLE IF NOT EXISTS acid_update(id BIGINT) tblproperties ("transactional"="true");

--插入数据。

INSERT OVERWRITE TABLE acid_update VALUES(1),(2),(3),(2);

--查看插入结果。

SELECT * FROM acid_update;

--返回结果

+------------+

| id |

+------------+

| 1 |

| 2 |

| 3 |

| 2 |

+------------+

--将所有id为2的行，id值更新为4。

UPDATE acid_update SET id = 4 WHERE id = 2;

--查看更新结果，2被更新为4。

SELECT * FROM acid_update;

--返回结果

+------------+

| id |

+------------+

| 1 |

| 3 |

| 4 |

| 4 |

+------------+示例2：创建分区表acid_update，并导入数据，执行UPDATE操作更新满足指定条件的行对应的列数据。命令示例如下：

--创建Transactional表acid_update_pt。

CREATE TABLE IF NOT EXISTS acid_update_pt(id BIGINT) PARTITIONED BY(ds STRING) tblproperties ("transactional"="true");

--添加分区。

ALTER TABLE acid_update_pt ADD IF NOT EXISTS PARTITION (ds= '2019');

--插入数据。

INSERT OVERWRITE TABLE acid_update_pt PARTITION (ds='2019') VALUES(1),(2),(3);

--查看插入结果

SELECT * FROM acid_update_pt WHERE ds = '2019';

--返回结果

+------------+------------+

| id | ds |

+------------+------------+

| 1 | 2019 |

| 2 | 2019 |

| 3 | 2019 |

+------------+------------+

--更新指定行的一列数据，将分区为2019的所有id=2的行，id值更新为4。

UPDATE acid_update_pt SET id = 4 WHERE ds = '2019' AND id = 2;

--查看更新结果，2被更新为4。

SELECT * FROM acid_update_pt WHERE ds = '2019';

--返回结果

+------------+------------+

| id | ds |

+------------+------------+

| 4 | 2019 |

| 1 | 2019 |

| 3 | 2019 |

+------------+------------+示例3：创建目标表acid_update_t和关联表acid_update_s，实现同时更新多列值。命令示例如下：

--创建待更新目标Transactional表acid_update_t和关联表acid_update_s。

CREATE TABLE IF NOT EXISTS acid_update_t(id INT,value1 INT,value2 INT) tblproperties ("transactional"="true");

CREATE TABLE IF NOT EXISTS acid_update_s(id INT,value1 INT,value2 INT);

--插入数据。

INSERT OVERWRITE TABLE acid_update_t VALUES(2,20,21),(3,30,31),(4,40,41);

INSERT OVERWRITE TABLE acid_update_s VALUES(1,100,101),(2,200,201),(3,300,301);

--方式一：用常量更新。

UPDATE acid_update_t SET (value1, value2) = (60,61);

--查询方式一目标表结果数据。

SELECT * FROM acid_update_t;

--返回结果

+------------+------------+------------+

| id | value1 | value2 |

+------------+------------+------------+

| 2 | 60 | 61 |

| 3 | 60 | 61 |

| 4 | 60 | 61 |

+------------+------------+------------+

--方式二：关联更新，规则为acid_update_t表左关联acid_update_s表。

UPDATE acid_update_t SET (value1, value2) = (SELECT value1, value2 FROM acid_update_s WHERE acid_update_t.id = acid_update_s.id);

--查询方式二目标表结果数据。

SELECT * FROM acid_update_t;

--返回结果

+------------+------------+------------+

| id | value1 | value2 |

+------------+------------+------------+

| 2 | 200 | 201 |

| 3 | 300 | 301 |

| 4 | NULL | NULL |

+------------+------------+------------+

--方式三（基于“方式二”的结果进行更新）：关联更新，规则为增加过滤条件，只更新交集。

UPDATE acid_update_t SET (value1, value2) = (SELECT value1, value2 FROM acid_update_s WHERE acid_update_t.id = acid_update_s.id) WHERE acid_update_t.id IN (SELECT id FROM acid_update_s);

--查询方式三目标表结果数据。

SELECT * FROM acid_update_t;

--返回结果

+------------+------------+------------+

| id | value1 | value2 |

+------------+------------+------------+

| 2 | 200 | 201 |

| 3 | 300 | 301 |

| 4 | NULL | NULL |

+------------+------------+------------+

--方式四（基于“方式三”的结果进行更新）：用汇总结果关联更新。

UPDATE acid_update_t SET (id, value1, value2) = (SELECT id, MAX(value1),MAX(value2) FROM acid_update_s WHERE acid_update_t.id = acid_update_s.id GROUP BY acid_update_s.id) WHERE acid_update_t.id IN (SELECT id FROM acid_update_s);

--查询方式四目标表结果数据。

SELECT * FROM acid_update_t;

--返回结果

+------------+------------+------------+

| id | value1 | value2 |

+------------+------------+------------+

| 2 | 200 | 201 |

| 3 | 300 | 301 |

| 4 | NULL | NULL |

+------------+------------+------------+示例4：涉及两个表的简单关联查询，示例命令如下：

--创建更新目标表acid_update_t和关联表acid_update_s

CREATE TABLE IF NOT EXISTS acid_update_t(id BIGINT,value1 BIGINT,value2 BIGINT) tblproperties ("transactional"="true");

CREATE TABLE IF NOT EXISTS acid_update_s(id BIGINT,value1 BIGINT,value2 BIGINT);

--插入数据

INSERT OVERWRITE TABLE acid_update_t VALUES(2,20,21),(3,30,31),(4,40,41);

INSERT OVERWRITE TABLE acid_update_s VALUES(1,100,101),(2,200,201),(3,300,301);

--查询acid_update_t表数据

SELECT * FROM acid_update_t;

--返回结果

+------------+------------+------------+

| id | value1 | value2 |

+------------+------------+------------+

| 2 | 20 | 21 |

| 3 | 30 | 31 |

| 4 | 40 | 41 |

+------------+------------+------------+

--查询acid_update_s表数据

SELECT * FROM acid_update_s;

--返回结果

+------------+------------+------------+

| id | value1 | value2 |

+------------+------------+------------+

| 1 | 100 | 101 |

| 2 | 200 | 201 |

| 3 | 300 | 301 |

+------------+------------+------------+

--关联更新，目标表增加过滤条件，只取交集

UPDATE acid_update_t SET value1 = b.value1, value2 = b.value2

FROM acid_update_s b WHERE acid_update_t.id = b.id;

-- 等价于上面写法

UPDATE acid_update_t a SET a.value1 = b.value1, a.value2 = b.value2

FROM acid_update_s b WHERE a.id = b.id;

--查看更新结果，20被更新为200，21被更新为201，30被更新为300，31被更新为301

SELECT * FROM acid_update_t;

--返回结果

+------------+------------+------------+

| id | value1 | value2 |

+------------+------------+------------+

| 4 | 40 | 41 |

| 2 | 200 | 201 |

| 3 | 300 | 301 |

+------------+------------+------------+ 示例5：涉及多个表的复杂关联查询，示例命令如下：

--创建更新目标表acid_update_t和关联表acid_update_s

CREATE TABLE IF NOT EXISTS acid_update_t(id BIGINT,value1 BIGINT,value2 BIGINT) tblproperties ("transactional"="true");

CREATE TABLE IF NOT EXISTS acid_update_s(id BIGINT,value1 BIGINT,value2 BIGINT);

CREATE TABLE IF NOT EXISTS acid_update_m(id BIGINT,value1 BIGINT,value2 BIGINT);

--插入数据

INSERT OVERWRITE TABLE acid_update_t VALUES(2,20,21),(3,30,31),(4,40,41),(5,50,51);

INSERT OVERWRITE TABLE acid_update_s VALUES (1,100,101),(2,200,201),(3,300,301),(4,400,401),(5,500,501);

INSERT OVERWRITE TABLE acid_update_m VALUES(3,30,101),(4,400,201),(5,300,301);

--查询acid_update_t表数据

SELECT * FROM acid_update_t;

--返回结果

+------------+------------+------------+

| id | value1 | value2 |

+------------+------------+------------+

| 2 | 20 | 21 |

| 3 | 30 | 31 |

| 4 | 40 | 41 |

| 5 | 50 | 51 |

+------------+------------+------------+

--查询acid_update_s表数据

SELECT * FROM acid_update_s;

--返回结果

+------------+------------+------------+

| id | value1 | value2 |

+------------+------------+------------+

| 1 | 100 | 101 |

| 2 | 200 | 201 |

| 3 | 300 | 301 |

| 4 | 400 | 401 |

| 5 | 500 | 501 |

+------------+------------+------------+

--查询acid_update_m表数据

SELECT * FROM acid_update_m;

--返回结果

+------------+------------+------------+

| id | value1 | value2 |

+------------+------------+------------+

| 3 | 30 | 101 |

| 4 | 400 | 201 |

| 5 | 300 | 301 |

+------------+------------+------------+

--关联更新，并且在where中同时对原表和目标表进行过滤

UPDATE acid_update_t

SET value1 = acid_update_s.value1, value2 = acid_update_s.value2

FROM acid_update_s

WHERE acid_update_t.id = acid_update_s.id

AND acid_update_s.id > 2

AND acid_update_t.value1 NOT IN

(SELECT value1 FROM acid_update_m WHERE id = acid_update_t.id)

AND acid_update_s.value1 NOT IN

(SELECT value1 FROM acid_update_m WHERE id = acid_update_s.id);

--查看更新结果，acid_update_t表只有id为5的数据符合条件，对应value1被更新为500，valu2被更新为501

SELECT * FROM acid_update_t;

--返回结果

+------------+------------+------------+

| id | value1 | value2 |

+------------+------------+------------+

| 5 | 500 | 501 |

| 2 | 20 | 21 |

| 3 | 30 | 31 |

| 4 | 40 | 41 |

+------------+------------+------------+示例6：创建Delta Table表mf_dt，并导入数据，执行UPDATE操作删除满足指定条件的行。命令示例如下：

--创建目标Delta Table表mf_dt。

CREATE TABLE IF NOT EXISTS mf_dt (pk BIGINT NOT NULL PRIMARY KEY,

val BIGINT NOT NULL)

PARTITIONED BY(dd STRING, hh STRING)

tblproperties ("transactional"="true");

--插入数据

INSERT OVERWRITE TABLE mf_dt PARTITION (dd='01', hh='02')

VALUES (1, 1), (2, 2), (3, 3);

--查看插入结果

SELECT * FROM mf_dt WHERE dd='01' AND hh='02';

--返回结果

+------------+------------+----+----+

| pk | val | dd | hh |

+------------+------------+----+----+

| 1 | 1 | 01 | 02 |

| 3 | 3 | 01 | 02 |

| 2 | 2 | 01 | 02 |

+------------+------------+----+----+

--更新指定行的一列数据，将分区为01和02的所有pk=3的行，val值更新为30。

--方法一

UPDATE mf_dt SET val = 30 WHERE pk = 3 AND dd='01' AND hh='02';

--方法二

UPDATE mf_dt SET val = delta.val

FROM (SELECT pk, val FROM VALUES (3, 30) t (pk, val)) delta

WHERE delta.pk = mf_dt.pk AND mf_dt.dd='01' AND mf_dt.hh='02';

--查看更新结果。

SELECT * FROM mf_dt WHERE dd='01' AND hh='02';

--返回结果，pk=3的行val值被更新为30。

+------------+------------+----+----+

| pk | val | dd | hh |

+------------+------------+----+----+

| 1 | 1 | 01 | 02 |

| 3 | 30 | 01 | 02 |

| 2 | 2 | 01 | 02 |

+------------+------------+----+----+