DELETE

Function

Deletes rows that satisfy the WHERE clause from the specified table. If the WHERE clause does not exist, all rows in the table are deleted, and only the table structure is retained.

Currently, only Iceberg tables support the DELETE operation.

Precautions

To delete data from a table, you must have the DELETE permission on the table. You must also have the SELECT permission on the table referenced by the USING clause and the table read in the condition.

Do not use DELETE to delete all data in a table. Consider using TRUNCATE TABLE instead.

Syntax

DELETE FROM table_name [ * ] [ [ AS ] alias ]
    [ USING using_list ]
    [ WHERE condition ]

Parameter Description

• table_name: name of the target table. Range: an existing table name.

• alias: alias of the target table. Range: a string. It must comply with the naming convention.
• using_list: USING clause.
• condition: an expression that returns a boolean value, used to determine which rows need to be deleted.

Examples

CREATE TABLE reason_t2
(
    TABLE_SK          INTEGER               ,
    TABLE_ID          VARCHAR(20)           ,
    TABLE_NA          VARCHAR(20)
) STORE AS iceberg;

INSERT INTO reason_t2 VALUES (1, 'S01', 'StudentA'),(2, 'T01', 'TeacherA'),(3, 'T02', 'TeacherB');

DELETE FROM reason_t2 WHERE TABLE_SK = 2;
DELETE FROM reason_t2 AS rt2 WHERE rt2.TABLE_SK = 2;

DELETE FROM reason_t2 WHERE TABLE_SK in (1,3);