DELETE

The DELETE statement can be used to remove rows from a table or from the base table of a view.

Syntax

DELETE
    [FROM] {table_expr_clause} [t_alias]
    [WHERE clause];

Parameters

FROM table_expr_clause: Specifies the object from which rows should be removed. See the section "table_expr_clause" below.

---

t_alias (optional): Alias for the table, view, materialized view, subquery, or collection value. t_alias is mandatory if the table_expr_clause references object type attributes or methods.

---

WHERE clause: See the WHERE clause section below.

table_expr_clause

{
  folder_name.table_name  
   | folder_name.view_name
   | (subquery)
}

Parameters

folder_name: The folder that contains the table or view.

---

table_name: The name of the table.

---

view_name: The name of the view. When using DELETE on an updatable view, data is removed from the underlying table.

---

subquery: A subquery. See the "subquery" section below.

Limitations of table_expr_clause

• ORDER BY clause is not supported in the subquery of table_expr_clause
• A set operator
• A DISTINCT operator
• An aggregate or analytic function
• A GROUP BY, ORDER BY, CONNECT BY, or START WITH clause
• A subquery in the SELECT list
• JOIN

WHERE Clause

WHERE condition

In general, the WHERE clause in a DELETE statement defines the rows to be deleted. Only rows that match the condition are removed. The condition may include object references and subqueries.

If the WHERE clause is omitted, all rows in the object are removed.

Parameter

condition: The condition that defines which rows should be deleted. See also WHERE.

Example 1 | DELETE ... WHERE

create table mkw_doku.my_table1 (ord string, col1 integer);

insert into mkw_doku.my_table1 (select 'a' as ord, 10 as col1
                       union all 
                       select 'b' as ord,  20 as col1
                       union all
                       select 'c' as ord,  30 as col1
                       union all
                       select 'd' as ord,  40 as col1
                       union all
                       select 'f' as ord,  null as col1);
                         
delete from mkw_doku.my_table1 where col1 >= 30;  
select * from mkw_doku.my_table1;

+-----+------+
| ord | col1 |
+-----+------+
| a   | 10   |
| b   | 20   |
| f   | NULL |
+-----+------+

Example 2 | DELETE with Subquery

CREATE TABLE mkw_doku.my_table1 (ord STRING, col1 INTEGER);

INSERT INTO mkw_doku.my_table1 
SELECT 'a', 10
UNION ALL 
SELECT 'b', 20
UNION ALL
SELECT 'c', 30
UNION ALL
SELECT 'd', 40
UNION ALL
SELECT 'f', NULL;

DELETE FROM (SELECT * FROM mkw_doku.my_table1) WHERE col1 >= 30 AND ord = 'd';  
SELECT * FROM mkw_doku.my_table1;

+-----+------+
| ord | col1 |
+-----+------+
| b   | 20   |
| c   | 30   |
| f   | NULL |
| a   | 10   |
+-----+------+