ByteHouse 支持使用更新语句（UPDATE）更新单表数据或多表关联数据。

• UPDATE 的目标表必须包含唯一键（unique key）， 普通 CnchMergeTree 目前不支持更新操作。
• 暂不支持唯一键和分区键列更新。
• 使用时尽量避免全表更新。

使用限制

当前仅唯一键表支持使用更新语句（UPDATE），其他类型的表请参考“ALTER TABLE - UPDATE”语句。

语法

UPDATE [db.]table SET a=b WHERE expr;

参数说明如下：

示例

-- 引擎默认保证 unique key 在分区内的唯一性
-- 注：UNIQUE KEY 不支持 Nullable
CREATE TABLE test.unique_table
(
  `event_time` DateTime,
  `product_id` UInt64,
  `city` String,
  `category` String,
  `amount` UInt32,
  `revenue` UInt64
)
ENGINE = CnchMergeTree
PARTITION BY toDate(event_time)
ORDER BY (city, category)
UNIQUE KEY product_id;

INSERT INTO test.unique_table VALUES
('2020-10-29 23:40:00', 10001, 'Beijing', '男装', 5, 500),
('2020-10-29 23:40:00', 10002, 'Beijing', '男装', 2, 200),
('2020-10-29 23:40:00', 10003, 'Beijing', '男装', 1, 100);

-- 通过 UPDATE 语句来进行更新
UPDATE test.unique_table SET amount = 10, revenue = 1000 WHERE event_time = '2020-10-29 23:40:00' and product_id=10001;

-- 查询结果中 10001 数据行进行了 UPDATE 变更
select * from test.unique_table order by toDate(event_time), product_id;
┌──────────event_time─┬─product_id─┬─city────┬─category─┬─amount─┬─revenue─┐
│ 2020-10-29 23:40:00 │      10001 │ Beijing │ 男装     │     10 │    1000 │
│ 2020-10-29 23:40:00 │      10002 │ Beijing │ 男装     │      2 │     200 │
│ 2020-10-29 23:40:00 │      10003 │ Beijing │ 男装     │      1 │     100 │
└─────────────────────┴────────────┴─────────┴──────────┴────────┴─────────┘

前提条件

请确认您使用的 ByteHouse 云数仓版为 2.2 及以上版本。您可通过 ByteHouse 控制台>租户管理>基本信息路径查看引擎版本。

语法

UPDATE Table_name1
[INNER JOIN | LEFT JOIN] Table_name2 ON Table_name1.C1 = Table_name2.C1
SET assignment_list
[WHERE where_condition]

参数说明如下：

UPDATE customer
    LEFT JOIN new_customer ON customer.customer_name = new_customer.customer_name
SET
    customer.customer_id = '1';

示例

多表更新以下表为例，演示如何使用 UPDATE JOIN 语句。示例中创建了表 customer 和 new_customer，分区键设置为 customer_name，唯一键设置为 customer_id。

CREATE TABLE customer(customer_id Int32, customer_name String, customer_age Int32, addr String)
ENGINE = CnchMergeTree()  
PARTITION BY hash(customer_name) % 10
ORDER BY customer_id
UNIQUE KEY customer_id;

CREATE TABLE new_customer(customer_id Int32, customer_name String, customer_age Int32, addr String) 
ENGINE = CnchMergeTree() 
PARTITION BY hash(customer_name) % 10
ORDER BY customer_id
UNIQUE KEY customer_id;

• 示例 1：LEFT JOIN 子句与 UPDATE 语句一起使用
  将 customer_id ='2369' 顾客的年龄更新为 42。

  UPDATE customer
      LEFT JOIN new_customer ON customer.customer_id = new_customer.customer_id
  SET
      customer.customer_age = 42
  WHERE
      new_customer.customer_id = '2369';
  

  您也可以使用以下命令，执行效果与使用上述命令相同。

  INSERT INTO customer
  SELECT customer.customer_id, customer.customer_name, 42, addr
  FROM customer 
  LEFT JOIN new_customer
  ON customer_customer_id = new_customer.customer_id
  WHERE new_customer.customer_id = '2369';
  

• 示例 2：INNER JOIN 子句与 UPDATE 语句一起使用

  • 将 customer 表中的 addr 更新为 'beijing' 。

    UPDATE customer
        INNER JOIN new_customer ON customer.customer_name = new_customer.customer_name 
    SET
        customer.addr = 'beijing';
    

    您也可以使用以下命令，执行效果与使用上述命令相同。

    INSERT INTO customer
    SELECT customer.customer_id, customer.customer_name, customer.customer_age, 'beijing'
    FROM customer 
    INNER JOIN new_customer
    ON customer.customer_name = new_customer.customer_name;
    

  • 将 customer 表中的 customer_name 更新为 new_customer 表中的 customer_name。

    UPDATE customer
        INNER JOIN new_customer ON customer.customer_id = new_customer.customer_id
    SET
        customer.customer_age = new_customer.customer_age;
    

    您也可以使用以下命令，执行效果与使用上述命令相同。

    INSERT INTO customer
    SELECT customer.customer_id, customer.customer_name, new_customer.customer_age, customer.addr
    FROM customer 
    INNER JOIN new_customer
    ON customer.customer_id = new_customer.customer_id;