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 table owner, users with the DELETE permission on the current table, and users with the DELETE ANY TABLE permission can delete data from the table. System administrators automatically have this permission when separation of duties is disabled. Additionally, you must have the SELECT permission on the tables referenced by the USING clause and the tables read by condition.
Currently, the syntax for deleting multiple tables cannot be used to delete views.
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 */] [IGNORE] FROM {table_name [partition_clause] | view_name}
    [ WHERE condition ]
    [ ORDER BY { expression  [ ASC | DESC ] } [ NULLS { FIRST | LAST } ] ]
    [ LIMIT { count } ];

Delete multiple tables:

[ WITH [ RECURSIVE ] with_query [, ...] ]
DELETE [/*+ plan_hint */] [IGNORE] 
    FROM table_name1[ .* ] [,table_name2[ .* ]] ...
    USING using_list
    [ WHERE condition ];

[ WITH [ RECURSIVE ] with_query [, ...] ]
DELETE [/*+ plan_hint */] [IGNORE]
    table_name1[ .* ][,table_name2[ .* ]] ...
    FROM using_list
    [ WHERE condition ];

Parameters

WITH [ RECURSIVE ] with_query [, ...]
Specifies one or more subqueries that can be referenced by name in the main query, which are equivalent to temporary tables. This subquery statement structure is called the common table expression (CTE) structure. When this structure is used, the execution plan contains the CTE SCAN content.

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

The detailed format for with_query is as follows:
```
with_query_name [ ( column_name [, ...] ) ] AS ( {select} )
```
- 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 names displayed in the subquery result set.
- You can use SELECT in the subqueries.
- RECURSIVE can appear only after WITH. If there are multiple CTEs, you only need to specify RECURSIVE at the first CTE.
- When RECURSIVE is used, the data types of the subquery results on both sides of UNION ALL and EXCEPT ALL or UNION [DISTINCT] and EXCEPT [DISTINCT] in the CTE subquery must be converted to the same data type using the CAST function. In addition, the precision and collation of the subquery results on both sides must be the same. For example, WITH RECURSIVE cte (n) AS ( SELECT cast(id as signed int) from table_1 UNION ALL SELECT cast((n + 1) as signed int) FROM cte WHERE n < 5 ) SELECT * FROM cte. For details about the type conversion generated by operators, see Specification Constraints of Logical Operators, Specification Constraints of Bitwise Operators, and Specification Constraints of Arithmetic Operators.
plan_hint
Follows the DELETE keyword in the /*+ */ format. It is used to optimize the plan of a DELETE statement block. In each statement, only the first /*+ plan_hint */ comment block takes effect as a hint. Multiple hints can be written.
IGNORE
When the DELETE statement uses the IGNORE keyword, some ERROR-level errors can be degraded to WARNING-level errors, and invalid values can be adjusted to the closest values based on error scenarios. GaussDB supports the following error downgrade scenarios:

sql_mode set to loose

IGNORE is not supported during upgrade observation.
table_name
Specifies the name (optionally schema-qualified) of the target table.

Value range: existing table names.
partition_clause
Deletes a specified partition.

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

For keywords, see SELECT.

For examples, see CREATE TABLE SUBPARTITION.
view_name
Specifies name of the target view.
The restrictions on deleting a view are as follows:
- The DELETE operation can be performed only on columns that directly reference user columns in the base table.
- A view must contain at least one updatable column. For details about updatable columns, see CREATE VIEW.
- Views that contain the DISTINCT, GROUP BY, HAVING, LIMIT, or OFFSET clause at the top layer are not supported.
- Views that contain set operations (UNION and EXCEPT) at the top layer are not supported.
- Views whose target lists contain aggregate functions, window functions, or return set functions (such as array_agg, json_agg, and generate_series) are not supported.
- Table types supported in views include ordinary tables, temporary tables, global temporary tables, partitioned tables, level-2 partitioned tables, Ustore tables, and Astore tables.
- A join view can delete only the key-preserved tables in the view. 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. 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.
NULLS FIRST
Specifies that null values appear before non-null values in the sort ordering. This is the default when DESC is specified.
NULLS LAST
Specifies that null values appear after non-null values in the sort ordering. This is the default when DESC is not specified.
using_list
Specifies the USING clause.
When multiple tables are to be deleted, the target tables can appear at the same time when using_list specifies the set of joined 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.

After m_format_dev_version is set to 's2', the matching condition is met only when all target tables exist in using_list and their table names, aliases, and database names are the same. If no unique match exists, the syntax fails to be executed. The matching rules are as follows:
1. If the target table is in the form of Database name.Table name, the table name column can only be set to a real table name, not an alias reference, and can only match objects whose aliases are not specified in using_list.
2. If no database name is specified for the target table, the database name in use is used by default.
3. If an alias is specified for an object in using_list, the object is matched by alias.
condition
Specifies an expression that returns a Boolean value. Only rows for which this expression returns true will be deleted. To prevent unexpected outcomes, avoid using numeric types such as int for condition, because these types can be implicitly converted into Boolean values: 0 becomes false, while other values become true.
ORDER BY
For keywords, see SELECT.
LIMIT
For keywords, see SELECT.

Example

Delete some data.

-- Create a table.
m_db=# CREATE TABLE test_t1(col1 INT,col2 INT);
m_db=# INSERT INTO test_t1 VALUES (1, 1), (2, 2), (3, 3), (4, 4), (4, 6);

-- Delete all records from a table.
m_db=# DELETE FROM test_t1 WHERE col1 = 4;

-- Query.
m_db=# SELECT * FROM test_t1;
 col1 | col2 
------+------
    1 |    1
    2 |    2
    3 |    3
(3 rows)

Delete all data.

-- Delete all data.
m_db=# DELETE FROM test_t1;

-- Query.
m_db=# SELECT * FROM test_t1;
 col1 | col2 
------+------
(0 rows)

-- Drop the table.
m_db=# DROP TABLE test_t1;

Delete a view.

-- Create a schema.
m_db=# CREATE SCHEMA del_view;
CREATE SCHEMA
m_db=# SET CURRENT_SCHEMA = 'del_view';
SET

-- Create a table and insert data into the table.
m_db=# CREATE TABLE t1 (x1 int, y1 int);
CREATE TABLE
m_db=# 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
m_db=# 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
m_db=# CREATE TABLE tinfo (z INT PRIMARY KEY, comm VARCHAR(20));
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "tinfo_pkey" for table "tinfo"
CREATE TABLE
m_db=# INSERT INTO t1 VALUES (1, 1), (2, 2), (3, 3), (5, 5);
INSERT 0 4
m_db=# INSERT INTO t2 VALUES (1, 1), (2, 2), (3, 3), (5, 5);
INSERT 0 4
m_db=# INSERT INTO tdata VALUES (1, 1), (2, 2), (3, 3);
INSERT 0 3
m_db=# INSERT INTO tinfo VALUES (1,'one'), (2, 'two'), (3, 'three'), (5, 'wrong three');
INSERT 0 4

-- Create a single table view.
m_db=# CREATE VIEW v_del1 AS SELECT * FROM t1;
CREATE VIEW

-- Delete data from t1 using a view.
m_db=# DELETE FROM v_del1 where y1 = 3;
DELETE 1

-- Create a multi-table join view.
m_db=# CREATE VIEW vvt1t2 AS SELECT * FROM t1, t2 WHERE x1 = x2;
CREATE VIEW
m_db=# 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

-- Delete the multi-table join view.
m_db=# SELECT * FROM vvt1t2;
 x1 | y1 | x2 | y2
----+----+----+----
  1 |  1 |  1 |  1
  2 |  2 |  2 |  2
  5 |  5 |  5 |  5
(3 rows)

m_db=# DELETE FROM vvt1t2 WHERE y2 = 5;
DELETE 1
m_db=# SELECT * FROM vvt1t2;
 x1 | y1 | x2 | y2
----+----+----+----
  1 |  1 |  1 |  1
  2 |  2 |  2 |  2
(2 rows)

m_db=# SELECT * FROM vv_dup;
 x1 | y1 | x2 | y2
----+----+----+----
  1 |  1 |  1 |  1
  2 |  2 |  2 |  2
  3 |  3 |  3 |  3
(3 rows)

m_db=# DELETE FROM vv_dup WHERE y1 = 2;
DELETE 1
m_db=# SELECT * FROM vv_dup;
 x1 | y1 | x2 | y2
----+----+----+----
  1 |  1 |  1 |  1
  3 |  3 |  3 |  3
(2 rows)

-- Drop the schema.
m_db=# DROP SCHEMA del_view;
NOTICE:  drop cascades to 7 other objects
DETAIL:  drop cascades to table t1
drop cascades to table t2
drop cascades to table tdata
drop cascades to table tinfo
drop cascades to view v_del1
drop cascades to view vvt1t2
drop cascades to view vv_dup
DROP SCHEMA