DELETE

Description

Deletes rows that satisfy the WHERE clause from the specified table. If the WHERE clause is absent, the effect is to delete all rows in the table. The result is a valid, but an empty table.

Precautions

• The owner of a table, users granted the DELETE permission on the table or the DELETE ANY TABLE permission can delete data from the table. When separation of duties is disabled, system administrators have this permission by default. To perform the DELETE operation, you must have the SELECT permission on the table referenced by the USING clause and the table read in the condition.
• For row-store replication tables, DELETE can be performed only in the following scenarios:
  • Scenarios with primary key constraints.
  • Scenarios where the execution plan can be pushed down.
• For a DELETE statement whose subquery is a STREAM plan, UPDATE cannot be performed on the deleted row data.

Syntax

1
2
3
4
5
6
7

[ WITH [ RECURSIVE ] with_query [, ...] ]
DELETE [/*+ plan_hint */] [FROM] [ ONLY ] {table_name [ * ] subquery [ [ AS ] alias ] | view_name [ [ AS ] alias ]}
    [ USING using_list ]
    [ WHERE condition | WHERE CURRENT OF cursor_name ] 
    [ ORDER BY {expression [  ASC | DESC | USING operator ] } ]
    [ LIMIT row_count ]
    [ RETURNING { * | { output_expr [ [ AS ] output_name ] } [, ...] } ];

Parameters

• WITH [ RECURSIVE ] with_query [, ...]

  Specifies one or more subqueries that can be referenced by name in the main query, which is equivalent to a temporary table.

  If RECURSIVE is specified, it allows a SELECT subquery to reference itself by name.

  • with_query_name specifies the name of the result set generated by a subquery. Such names can be used to access the result sets of subqueries in a query.
  • column_name specifies the column name displayed in the subquery result set.
  • Each subquery can be a SELECT, VALUES, INSERT, UPDATE, or DELETE statement.
  • You can use MATERIALIZED or NOT MATERIALIZED to modify the CTE.
    • If MATERIALIZED is specified, the WITH query will be materialized, and a copy of the subquery result set is generated. The copy is directly queried at the reference point. Therefore, the WITH subquery cannot be jointly optimized with the SELECT statement trunk (for example, predicate pushdown and equivalence class transfer). In this scenario, you can use NOT MATERIALIZED for modification. If the WITH query can be executed as a subquery inline, the preceding optimization can be performed.
    • If the user does not explicitly declare the materialized attribute, comply with the following rules: If the CTE is referenced only once in the trunk statement to which it belongs and semantically supports inline execution, it will be rewritten as subquery inline execution. Otherwise, the materialized execution will be performed in CTE Scan mode.
• plan_hint

  Follows the DELETE keyword in the /*+ */ format. It is used to optimize the plan of a DELETE statement block. For details, see Hint-based Optimization. In each statement, only the first /*+ plan_hint */ comment block takes effect as a hint. Multiple hints can be written.

• ONLY

  If ONLY is specified before the table name, matching rows are deleted from the named table only. If ONLY is not specified, matching rows are also deleted from any tables inheriting from the named table.

• table_name

  Specifies the name (optionally schema-qualified) of the target table.

  Value range: an existing table name

  

  You can use database links to perform operations on remote tables. For details, see DATABASE LINK.

• subquery

  The object to be deleted can be a subquery. When data in a subquery is deleted, the subquery is regarded as a temporary view. The CHECK OPTION option can be added to the end of the subquery.

  [ WITH [ RECURSIVE ] with_query [, ...] ]
  SELECT [/*+ plan_hint */] [ ALL ]
  { * | {expression [ [ AS ] output_name ]} [, ...] }
  [ into_option ]
  [ FROM from_item [, ...] ]
  [ WHERE condition ]
  [ [ START WITH condition ] CONNECT BY [NOCYCLE] condition [ ORDER SIBLINGS BY expression ] ]
  [ ORDER BY {expression [ [ ASC | DESC | USING operator ] | nlssort_expression_clause ] [ NULLS { FIRST | LAST } ]} [, ...] ]
  [ FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } ONLY ]
  [ into_option ];

  The specified subquery source from_item is as follows:

  {[ ONLY ] {table_name | view_name} [ * ] [ [ AS ] alias [ ( column_alias [, ...] ) ] ]
  |( select ) [ AS ] alias [ ( column_alias [, ...] ) ]
  |with_query_name [ [ AS ] alias [ ( column_alias [, ...] ) ] ]
  |from_item [ NATURAL ] join_type from_item [ ON join_condition | USING ( join_column [, ...] ) ]}

  If there is only one table in the subquery, the table is deleted. If there are multiple tables or nested tables in the subquery, the system determines whether the table can be deleted by checking whether there is a key-preserved table. For details about key-preserved tables and WITH CHECK OPTION, see CREATE VIEW.

• view_name

  Name of the target view.

  

  The restrictions on deleting views and subqueries are as follows:

  • The DELETE operation can be performed only on columns that directly reference user columns in the base table.
  • A subquery or view must contain at least one updatable column. For details about updatable columns, see CREATE VIEW.
  • Views and subqueries that contain the DISTINCT, GROUP BY, HAVING, LIMIT or OFFSET clause at the top layer are not supported.
  • Views and subqueries that contain set operations (UNION, INTERSECT, EXCEPT, and MINUS) at the top layer are not supported.
  • Views and subqueries whose target lists contain aggregate functions, window functions, or return set functions (such as array_agg, json_agg, and generate_series) are not supported.
  • Views with BEFORE or AFTER triggers but without INSTEAD OF triggers or INSTEAD rules are not supported.
  • Table types supported in views and subqueries include ordinary tables, temporary tables, global temporary tables, partitioned tables, level-2 partitioned tables, Ustore tables, and Astore tables.
  • A join view or subquery can delete only the key-preserved tables in the view or subquery. If there is only one key-preserved table, the data in the table is deleted. If there are multiple key-preserved tables, only the data in the first key-preserved table following FROM is deleted.
  • If the CHECK OPTION option is specified in the join view or subquery, the base table is duplicate, and the duplicate base table retains inconsistent key table attributes in the view or subquery, rows cannot be deleted from the join view or subquery. For details about the key-preserved table, see CREATE VIEW.
  • System views cannot be deleted.
  • Deleting multiple tables is not supported.
• alias

  Specifies a substitute name for the target table.

  Value range: a string. It must comply with the naming convention.

• using_list

  Specifies the USING clause.

• condition

  Specifies an expression that returns a value of type Boolean. Only rows for which this expression returns true will be deleted. You are advised not to use numeric types such as int as conditions, because such types can be implicitly converted to bool values (non-zero values are implicitly converted to true and 0 is implicitly converted to false), which may cause unexpected results.

• WHERE CURRENT OF cursor_name

  When the cursor points to a row in a table, you can use this syntax to delete the row. For details about the restrictions, see UPDATE.

• ORDER BY

  For details about the keywords, see SELECT.

• LIMIT

  For details about the keywords, see SELECT.

• output_expr

  Specifies an expression to be computed and returned by the DELETE statement after each row is deleted. The expression can use any column names of the table. Write * to return all columns.

• output_name

  Specifies a name to use for a returned column.

  Value range: a string. It must comply with the naming convention.

Examples

• Deleting some data records

  -- Create a table.
  gaussdb=# CREATE TABLE test_t1(col1 INT,col2 INT);
  gaussdb=# INSERT INTO test_t1 VALUES (1, 1), (2, 2), (3, 3), (4, 4), (4, 6);
  
  -- Delete some data records from the table.
  gaussdb=# DELETE FROM test_t1 WHERE col1 = 4;
  
  -- Query.
  gaussdb=# SELECT * FROM test_t1;
   col1 | col2 
  ------+------
      1 |    1
      2 |    2
      3 |    3
  (3 rows)

• Deleting all data

  -- Delete all data.
  gaussdb=# DELETE FROM test_t1;
  
  -- Query.
  gaussdb=# SELECT * FROM test_t1;
   col1 | col2 
  ------+------
  (0 rows)
  
  -- Drop the table.
  gaussdb=# DROP TABLE test_t1;

• WITH [ RECURSIVE ] with_query [, ...]

  -- Create a student table.
  gaussdb=# CREATE TABLE student(id INT,name varchar(50));
  
  -- Create a score table.
  gaussdb=# CREATE TABLE grade(id INT,score CHAR);
  
  gaussdb=# INSERT INTO student VALUES (1, 'tom'), (2, 'jerry'), (3, 'david');
  gaussdb=# INSERT INTO grade VALUES (1, 'A'), (2, 'B'), (3, 'b');
  
  -- Delete the data whose id is 2 from the student table and delete the data of the student from the grade table.
  gaussdb=# WITH del_stu AS(DELETE FROM student WHERE id = 2 RETURNING id)
      DELETE FROM grade WHERE id = (SELECT id FROM del_stu);
  
  -- Query data.
  gaussdb=# SELECT * FROM student;
   id | name  
  ----+-------
    1 | tom
    3 | david
  (2 rows)
  
  gaussdb=# SELECT * FROM grade;
   id | score 
  ----+-------
    1 | A
    3 | b
  (2 rows)
  
  -- Drop the table.
  gaussdb=# DROP TABLE grade;
  gaussdb=# DROP TABLE student;

• Deleting a view or subquery

  Example 1: Deleting a subquery

  -- Create a schema.
  gaussdb=# CREATE SCHEMA del_subqry;
  CREATE SCHEMA
  gaussdb=# SET CURRENT_SCHEMA = 'del_subqry';
  SET
  
  -- Create tables and insert data into the tables.
  gaussdb=# CREATE TABLE t1 (x1 int, y1 int);
  NOTICE:  The 'DISTRIBUTE BY' clause is not specified. Using 'x1' as the distribution column by default.
  HINT:  Please use 'DISTRIBUTE BY' clause to specify suitable data distribution column.
  CREATE TABLE
  gaussdb=# CREATE TABLE t2 (x2 int PRIMARY KEY, y2 int);
  NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "t2_pkey" for table "t2"
  CREATE TABLE
  gaussdb=# CREATE TABLE tdata (x INT PRIMARY KEY, y INT);
  NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "tdata_pkey" for table "tdata"
  CREATE TABLE
  gaussdb=# CREATE TABLE tinfo (z INT PRIMARY KEY, comm VARCHAR2(20));
  NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "tinfo_pkey" for table "tinfo"
  CREATE TABLE
  gaussdb=# INSERT INTO t1 VALUES (1, 1), (2, 2), (3, 3), (5, 5);
  INSERT 0 4
  gaussdb=# INSERT INTO t2 VALUES (1, 1), (2, 2), (3, 3), (5, 5);
  INSERT 0 4
  gaussdb=# INSERT INTO tdata VALUES (1, 1), (2, 2), (3, 3);
  INSERT 0 3
  gaussdb=# INSERT INTO tinfo VALUES (1,'one'), (2, 'two'), (3, 'three'), (5, 'wrong three');
  INSERT 0 4
  
  -- Delete data from t1 using a subquery.
  gaussdb=# DELETE FROM (SELECT * FROM t1) where y1 = 3;
  DELETE 1
  
  -- The subquery is read-only, and data cannot be deleted.
  gaussdb=# DELETE FROM (SELECT * FROM t1 WITH READ ONLY) WHERE y1 = 1;
  ERROR:  cannot perform a DML operation on a read-only subquery.
  
  -- Delete a subquery of a multi-table join.
  gaussdb=# SELECT * FROM t1, t2 WHERE x1 = x2;
   x1 | y1 | x2 | y2
  ----+----+----+----
    1 |  1 |  1 |  1
    2 |  2 |  2 |  2
    5 |  5 |  5 |  5
  (3 rows)
  
  gaussdb=# DELETE FROM (SELECT * FROM t1, t2 WHERE x1 = x2) WHERE y2 = 5;
  DELETE 1
  gaussdb=# SELECT * FROM t1, t2 WHERE x1 = x2;
   x1 | y1 | x2 | y2
  ----+----+----+----
    1 |  1 |  1 |  1
    2 |  2 |  2 |  2
  (2 rows)
  
  -- The subquery contains CHECK OPTION, and the tdata table is duplicate. td1 is not a key-preserved table, and td2 is a key-preserved table.
  gaussdb=# DELETE FROM (SELECT td1.x x1, td1.y y1, td2.x x2, td2.y y2 FROM tdata td1, tdata td2, tinfo WHERE td2.y=tinfo.z AND td1.x=td2.y WITH CHECK OPTION) WHERE y1 = 2;
  ERROR:  cannot delete from view without exactly one key-preserved table
  
  -- If CHECK OPTION is not specified, a subquery with the same structure is created and deleted successfully.
  gaussdb=# DELETE FROM (SELECT td1.x x1, td1.y y1, td2.x x2, td2.y y2 FROM tdata td1, tdata td2, tinfo WHERE td2.y=tinfo.z AND td1.x=td2.y) WHERE y1 = 2;
  DELETE 1
  -- Delete a schema.
  gaussdb=# RESET CURRENT_SCHEMA;
  RESET
  gaussdb=# DROP SCHEMA del_subqry CASCADE;
  NOTICE:  drop cascades to 5 other objects
  DETAIL:  drop cascades to table del_subqry.t1
  drop cascades to table del_subqry.t2
  drop cascades to table del_subqry.tdata
  drop cascades to table del_subqry.tinfo
  drop cascades to view del_subqry.vv_wco
  DROP SCHEMA

  Example 2: Deleting a view

  -- Create a schema.
  gaussdb=# CREATE SCHEMA del_view;
  CREATE SCHEMA
  gaussdb=# SET CURRENT_SCHEMA = 'del_view';
  SET
  
  -- Create tables and insert data into the tables.
  gaussdb=# CREATE TABLE t1 (x1 int, y1 int);
  NOTICE:  The 'DISTRIBUTE BY' clause is not specified. Using 'x1' as the distribution column by default.
  HINT:  Please use 'DISTRIBUTE BY' clause to specify suitable data distribution column.
  CREATE TABLE
  gaussdb=# CREATE TABLE t2 (x2 int PRIMARY KEY, y2 int);
  NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "t2_pkey" for table "t2"
  CREATE TABLE
  gaussdb=# CREATE TABLE tdata (x INT PRIMARY KEY, y INT);
  NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "tdata_pkey" for table "tdata"
  CREATE TABLE
  gaussdb=# CREATE TABLE tinfo (z INT PRIMARY KEY, comm VARCHAR2(20));
  NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "tinfo_pkey" for table "tinfo"
  CREATE TABLE
  gaussdb=# INSERT INTO t1 VALUES (1, 1), (2, 2), (3, 3), (5, 5);
  INSERT 0 4
  gaussdb=# INSERT INTO t2 VALUES (1, 1), (2, 2), (3, 3), (5, 5);
  INSERT 0 4
  gaussdb=# INSERT INTO tdata VALUES (1, 1), (2, 2), (3, 3);
  INSERT 0 3
  gaussdb=# INSERT INTO tinfo VALUES (1,'one'), (2, 'two'), (3, 'three'), (5, 'wrong three');
  INSERT 0 4
  
  -- Create a single table view.
  gaussdb=# CREATE VIEW v_del1 AS SELECT * FROM t1;
  CREATE VIEW
  gaussdb=# CREATE VIEW v_del_read AS SELECT * FROM t1 WITH READ ONLY;
  CREATE VIEW
  
  -- Delete data from t1 using a view.
  gaussdb=# DELETE FROM v_del1 where y1 = 3;
  DELETE 1
  
  -- The view is read-only, and data cannot be deleted.
  gaussdb=# DELETE FROM v_del_read WHERE y1 = 1;
  ERROR:  cannot perform a DML operation on a read-only subquery.
  
  -- Create multi-table join views.
  gaussdb=# CREATE VIEW vvt1t2 AS SELECT * FROM t1, t2 WHERE x1 = x2;
  CREATE VIEW
  gaussdb=# CREATE VIEW vv_dup AS SELECT td1.x x1, td1.y y1, td2.x x2, td2.y y2 FROM tdata td1, tdata td2, tinfo WHERE td2.y=tinfo.z AND td1.x=td2.y;
  CREATE VIEW
  gaussdb=# CREATE VIEW vv_dup_wco AS SELECT td1.x x1, td1.y y1, td2.x x2, td2.y y2 FROM tdata td1, tdata td2, tinfo WHERE td2.y=tinfo.z AND td1.x=td2.y WITH CHECK OPTION;
  CREATE VIEW
  
  -- Delete a multi-table join view.
  gaussdb=# SELECT * FROM vvt1t2;
   x1 | y1 | x2 | y2
  ----+----+----+----
    1 |  1 |  1 |  1
    2 |  2 |  2 |  2
    5 |  5 |  5 |  5
  (3 rows)
  
  gaussdb=# DELETE FROM vvt1t2 WHERE y2 = 5;
  DELETE 1
  
  gaussdb=# SELECT * FROM vvt1t2;
   x1 | y1 | x2 | y2
  ----+----+----+----
    1 |  1 |  1 |  1
    2 |  2 |  2 |  2
  (2 rows)
  
  -- The view contains CHECK OPTION, and the tdata table is duplicate. td1 is not a key-preserved table, and td2 is a key-preserved table.
  gaussdb=# DELETE FROM vv_dup_wco WHERE y1 = 2;
  ERROR:  cannot delete from view without exactly one key-preserved table
  
  -- If CHECK OPTION is not specified, a view with the same structure is created and deleted successfully.
  gaussdb=# DELETE FROM vv_dup WHERE y1 = 2;
  DELETE 1
  
  -- Delete a schema.
  gaussdb=# RESET CURRENT_SCHEMA;
  RESET
  gaussdb=# DROP SCHEMA del_view CASCADE;
  NOTICE:  drop cascades to 9 other objects
  DETAIL:  drop cascades to table del_view.t1
  drop cascades to table del_view.t2
  drop cascades to table del_view.tdata
  drop cascades to table del_view.tinfo
  drop cascades to view del_view.v_del1
  drop cascades to view del_view.v_del_read
  drop cascades to view del_view.vvt1t2
  drop cascades to view del_view.vv_dup
  drop cascades to view del_view.vv_dup_wco
  DROP SCHEMA

Suggestions

• DELETE

  To delete all records in a table, use the TRUNCATE syntax.