DELETE

Description

  1. This statement is used to conditionally delete data in the specified table (base index) partition.
  2. This action deletes the rollup index data associated with this base index at the same time.
  3. Grammar:
  4.     DELETE FROM table_name [PARTITION partition_name]
  5.     WHERE
  6.     column_name1 op { value | value_list } [ AND column_name2 op { value | value_list } ...];
  8. Explain:
  9.     1) Optional types of OP include: =, >, <, >=, <=, <=, <=, !=, in, not in
  10.     2) Conditions on key columns can only be specified.
  11.     2) When the selected key column does not exist in a rollup, delete cannot be performed.
  12.     3) The relationship between conditions can only be "and".
  13.     If you want to achieve the "or" relationship, you need to divide the conditions into two DELETE statements.
  14.     4) If it is a RANGE partitioned table, you can specify the partition. If not specified, and the session variable delete_without_partition is true, it will be applied to all partitions. If it is a single partition table, you do not need to specify it.
  16. Notice:
  17.     This statement may reduce query efficiency for a period of time after execution.
  18.     The degree of impact depends on the number of deletion conditions specified in the statement.
  19.     The more conditions specified, the greater the impact.

example

  1. 1. Delete rows whose K1 column value is 3 in my_table partition p 1
  2. DELETE FROM my_table PARTITION p1
  3. WHERE k1 = 3;
  5. 2. Delete rows whose K1 column value is greater than or equal to 3 and whose K2 column value is "abc" in my_table partition P1
  6. DELETE FROM my_table PARTITION p1
  7. WHERE k1 >= 3 AND k2 = "abc";
  9. 2. Delete rows whose K1 column value is greater than or equal to 3 and whose K2 column value is "abc" in my_table partition P1,P2
  10. DELETE FROM my_table PARTITIONS (p1, p2)
  11. WHERE k1 >= 3 AND k2 = "abc";

keyword

  1. DELETE