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 with the DELETE permission on the table, or users granted with the DELETE ANY TABLE permission can delete data from the table. When separation of duties is disabled, a system administrator has the permission to delete data from the table by default, as well as the SELECT permission on any table in the USING clause or whose values are read in condition.
• For row-store replication tables, DELETE can be performed only in the following two scenarios:
  • Scenarios with primary key constraints.
  • Scenarios where the execution plan can be pushed down.
• The syntax for deleting multiple tables is not applicable to views and tables containing RULE.
• For a DELETE statement whose subquery is a STREAM plan, UPDATE cannot be performed on the deleted row data.

Syntax

Delete a single table:

[ WITH [ RECURSIVE ] with_query [, ...] ]
DELETE [/*+ plan_hint */] [FROM] [ ONLY ] {table_name [ * ] [ [ [partition_clause]  [ [ AS ] alias ] ] | [ [ [ AS ] alias ] [partitions_clause] ] ] | 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 { 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 Tuning. 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 } ]} [, ...] ]
  [ OFFSET start [ ROW | ROWS ] ]
  [ FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } ONLY ]
  [ into_option ];

  The specified subquery source from_item is as follows:

  {[ ONLY ] {table_name | view_name} [ * ] [ partition_clause ] [ [ 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

  Specifies 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.
  • The DELETE operation cannot be performed on the system view.
  • Deleting multiple tables is not supported.
• partition_clause

  Deletes a specified partition.

  PARTITION { ( partition_name ) | FOR ( partition_value [, ...] ) } |

  SUBPARTITION { ( subpartition_name ) | FOR ( subpartition_value [, ...] ) }

  For details about the keywords, see SELECT.

  For details, see CREATE TABLE SUBPARTITION.

• partitions_clause

  Deletes multiple partitions.

  PARTITION { ( { partition_name | subpartition_name } [, ...] ) }

  This syntax takes effect only when sql_compatibility is set to B.

  For details about the keywords, see SELECT.

  For details, see CREATE TABLE SUBPARTITION.

• 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.

  

  When sql_compatibility is set to B or multiple tables are to be deleted, the target tables can appear at the same time when using_list specifies the set of associated tables. In addition, the aliases of the tables can be defined and used in the target tables. In other situations, the target tables cannot appear repeatedly in using_list.

• condition

  Specifies an expression that returns a Boolean value. 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 used to calculate the output result after a row is deleted using the DELETE statement. The expression can use any column of the table. You can use * to return all columns of the deleted row.

• 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)
  
  -- Delete the table.
  gaussdb=# DROP TABLE test_t1;

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

  -- Student table.
  gaussdb=# CREATE TABLE student(id INT,name varchar(50));
  
  -- Grade 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)
  
  -- Delete 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);
  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 4 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 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);
  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;
  DELTE 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.