ByteHouse Support project use Update statements (UPDATE) to update single-table Data or multi-table Linked data.

• The target table for UPDATE must contains a unique Key (unique key). Normal CnchMergeTree currently Not supported Actions for Update.
• Not supported yet for updating unique Key and Partition Key Column.
• Try to avoid updating the entire table when use.

limitations #

Currently, only tables with unique Key Support project use Update statements (UPDATE). For Other Type tables, please refer to the "ALTER TABLE - UPDATE" statement.

Syntax #

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

The parameter description is as follows:

| | | \

Example #

-- The engine Default guarantees the uniqueness of the unique key in the Partition 
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', 'Men's clothing', 5, 500), 
('2020-10-29 23:40:00', 10002, 'Beijing', 'Men's clothing', 2, 200), 
('2020-10-29 23:40:00', 10003, 'Beijing', 'Men's clothing', 1, 100); 

-- Pass the UPDATE statement to Carry on Update 
UPDATE test.unique_table SET amount = 10, revenue = 1000 WHERE event_time = '2020-10-29 23:40:00' and product_id=10001; 

-- In the Query results, the Data row with 10001 has undergone an UPDATE change 
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 │ Men's clothing     │     10 │    1000 │ 
│ 2020-10-29 23:40:00 │      10002 │ Beijing │ Men's clothing     │      2 │     200 │ 
│ 2020-10-29 23:40:00 │      10003 │ Beijing │ Men's clothing     │      1 │     100 │ 
└─────────────────────┴────────────┴─────────┴──────────┴────────┴─────────┘

prerequisites #

Confirm that the ByteHouse cloud Data warehouse Version you use is 2.2 or above. You can View the engine Version via the ByteHouse Console>Tenant manage>basic information Path.

Syntax #

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

The parameter description is as follows:

| | | \

Example #

Using the following tables as an Example, Instructions for how to use the UPDATE JOIN statement. In the Example, tables customer and new_customer are create, Partition Key settings as customer_name, unique Key settings as 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;

• Example 1: Using LEFT JOIN clause with UPDATE statement
  Update the Age of the customer with customer_id ='2369' to 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';
  

  You can also use the following command, which Run with the same effect as the command above.

  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';
  

• Example 2: Using INNER JOIN clause with UPDATE statement

  • Update addr in the customer table to 'beijing'.

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

    You can also use the following command, and the result will be the same as using the above command.

    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;
    

  • Update the customer_name in the customer table with the customer_name in the new_customer table.

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

    You can also use the following command, and running it will have the same effect as using the above command.

    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;