MaxCompute:DELETE

Prerequisites

Before you run a DELETE or UPDATE statement, you must have Select and Update permissions on the target Transactional table or Delta Table. For more information about authorization, see MaxCompute permissions.

Limits

Precautions

Syntax

DELETE FROM <table_name> [[AS] alias] [WHERE <condition>];

Parameters

Examples

• Example 1: Create a non-partitioned table named acid_delete, insert data, and then run the DELETE statement to delete rows that meet a specified condition.

  --Create a Transactional table named acid_delete.
  CREATE TABLE IF NOT EXISTS acid_delete (id BIGINT) TBLPROPERTIES ("transactional"="true");
  
  --Insert data.
  INSERT OVERWRITE TABLE acid_delete VALUES (1), (2), (3), (2);
  
  --View the inserted data.
  SELECT * FROM acid_delete;
  
  +------------+
  | id         |
  +------------+
  | 1          |
  | 2          |
  | 3          |
  | 2          |
  +------------+
  
  -- Delete the rows where id is 2. If you run this statement in the MaxCompute client (odpscmd), you must enter yes or no to confirm the deletion.
  DELETE FROM acid_delete WHERE id = 2;
  --This is equivalent to the preceding statement.
  DELETE FROM acid_delete ad WHERE ad.id = 2;
  
  --View the result. The table contains only the rows where id is 1 or 3.
  SELECT * FROM acid_delete;
  
  +------------+
  | id         |
  +------------+
  | 1          |
  | 3          |
  +------------+

• Example 2: Create a partitioned table named acid_delete_pt, insert data, and then run the DELETE statement to delete rows that meet a specified condition.

  --Create a Transactional table named acid_delete_pt.  
  CREATE TABLE IF NOT EXISTS acid_delete_pt (id BIGINT) PARTITIONED BY (ds STRING) TBLPROPERTIES ("transactional"="true");
  
  --Add partitions.
  ALTER TABLE acid_delete_pt ADD IF NOT EXISTS PARTITION (ds = '2019');
  ALTER TABLE acid_delete_pt ADD IF NOT EXISTS PARTITION (ds = '2018');
  
  --Insert data.
  INSERT OVERWRITE TABLE acid_delete_pt PARTITION (ds = '2019') VALUES (1), (2), (3);
  INSERT OVERWRITE TABLE acid_delete_pt PARTITION (ds = '2018') VALUES (1), (2), (3);
  
  --View the inserted data.
  SELECT * FROM acid_delete_pt;
  
  +------------+------------+
  | id         | ds         |
  +------------+------------+
  | 1          | 2018       |
  | 2          | 2018       |
  | 3          | 2018       |
  | 1          | 2019       |
  | 2          | 2019       |
  | 3          | 2019       |
  +------------+------------+
  
  --Delete the rows where the partition is 2019 and id is 2. If you run this statement in the MaxCompute client (odpscmd), you must enter yes or no to confirm the deletion.
  DELETE FROM acid_delete_pt WHERE ds = '2019' AND id = 2;
  
  --View the result. The rows where the partition is 2019 and id is 2 are deleted.
  SELECT * FROM acid_delete_pt;
  
  +------------+------------+
  | id         | ds         |
  +------------+------------+
  | 1          | 2018       |
  | 2          | 2018       |
  | 3          | 2018       |
  | 1          | 2019       |
  | 3          | 2019       |
  +------------+------------+

• Example 3: Create a destination table named acid_delete_t and an associated table named acid_delete_s. Then, delete the rows that meet the specified conditions from the destination table based on the associated table. Sample statements:

  --Create a target Transactional table named acid_delete_t and an associated table named acid_delete_s. 
  CREATE TABLE IF NOT EXISTS acid_delete_t (id INT, value1 INT, value2 INT) TBLPROPERTIES ("transactional"="true");
  CREATE TABLE IF NOT EXISTS acid_delete_s (id INT, value1 INT, value2 INT);
  
  --Insert data.
  INSERT OVERWRITE TABLE acid_delete_t VALUES (2, 20, 21), (3, 30, 31), (4, 40, 41);
  INSERT OVERWRITE TABLE acid_delete_s VALUES (1, 100, 101), (2, 200, 201), (3, 300, 301);
  
  --Delete rows from the acid_delete_t table whose id does not match any id in the acid_delete_s table. If you run this statement in the MaxCompute client (odpscmd), you must enter yes or no to confirm the deletion.
  DELETE FROM acid_delete_t WHERE NOT EXISTS (SELECT * FROM acid_delete_s WHERE acid_delete_t.id = acid_delete_s.id);
  --This is equivalent to the preceding statement.
  DELETE FROM acid_delete_t a WHERE NOT EXISTS (SELECT * FROM acid_delete_s b WHERE a.id = b.id);
  
  --View the result. The table contains only the rows where id is 2 or 3.
  SELECT * FROM acid_delete_t;
  
  +------------+------------+------------+
  | id         | value1     | value2     |
  +------------+------------+------------+
  | 2          | 20         | 21         |
  | 3          | 30         | 31         |
  +------------+------------+------------+

• Example 4: Create a Delta Table named mf_dt, insert data, and then run the DELETE statement to delete rows that meet a specified condition.

  --Create a target Delta Table named 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 data.
  INSERT OVERWRITE TABLE mf_dt PARTITION (dd='01', hh='02') VALUES (1, 1), (2, 2), (3, 3);
  
  --View the inserted data.                 
  SELECT * FROM mf_dt WHERE dd='01' AND hh='02';
  
  --The following result is returned.
  +------------+------------+----+----+
  | pk         | val        | dd | hh |
  +------------+------------+----+----+
  | 1          | 1          | 01 | 02 |
  | 3          | 3          | 01 | 02 |
  | 2          | 2          | 01 | 02 |
  +------------+------------+----+----+
  
  --Delete the rows where val is 2 and the partition is dd='01' and hh='02'.
  DELETE FROM mf_dt WHERE val = 2  AND dd='01' AND hh='02';
  
  --View the result. The table contains only the rows where val is 1 or 3.
  SELECT * FROM mf_dt WHERE dd='01' AND hh='02';
  
  --The following result is returned.
  +------------+------------+----+----+
  | pk         | val        | dd | hh |
  +------------+------------+----+----+
  | 1          | 1          | 01 | 02 |
  | 3          | 3          | 01 | 02 |
  +------------+------------+----+----+

Related statements

• UPDATE: updates the values of one or more columns of the rows that meet the specified conditions in partitioned transactional tables or non-partitioned transactional tables.

• ALTER TABLE: merges transactional table files.