Use the DELETE command to delete rows from a table - PolarDB

Removes rows from a table.

Syntax

DELETE [ optimizer_hint ] FROM table[subquery][@dblink ]
  [ WHERE condition ]
  [ RETURNING return_expression [, ...]
      { INTO { record | variable [, ...] }
      | BULK COLLECT INTO collection [, ...] } ]

Prerequisites

DELETE privilege on the target table
SELECT privilege on any table referenced in the WHERE condition

Description

DELETE removes rows that match the WHERE condition from the specified table. If you omit WHERE, all rows are removed and the table is left empty.

Note TRUNCATE is faster for removing all rows from a table.

RETURNING INTO clause

The RETURNING INTO { record | variable [, ...] } clause is only valid inside an SPL program.

The result set must contain at most one row. If it contains multiple rows, an exception is raised.
If the result set is empty, the target record or variables are set to null.

RETURNING BULK COLLECT INTO clause

The RETURNING BULK COLLECT INTO collection [, ...] clause is only valid inside an SPL program.

The result set may contain zero, one, or multiple rows.
Each return_expression is evaluated for every row in the result set and stored as an element in the corresponding collection, starting from the first element. Any existing content in collection is deleted.
If you specify multiple collections as targets, each collection must contain a scalar field — record types are not allowed.
If the result set is empty, collection is empty.

Parameters

Parameter	Description
`optimizer_hint`	Comment-embedded hints passed to the query optimizer to influence execution plan selection.
`table`	The name of the table to delete from. The name can be schema-qualified.
`subquery`	A subquery whose result set is treated as the deletion target.
`dblink`	The database link name that identifies a remote database. For details, see `CREATE DATABASE LINK`.
`condition`	A Boolean expression that determines which rows to delete.
`return_expression`	An expression based on one or more columns in `table`. When evaluated, the column values come from the deleted row.
`record`	An SPL record whose fields receive the evaluated `return_expression` values in order. The number of fields must match the number of expressions, and types must be compatible.
`variable`	An SPL variable that receives the evaluated `return_expression` value. When multiple variables are specified, each expression maps to the corresponding variable in order. The number of variables after `INTO` must match the number of expressions after `RETURNING`, and types must be compatible.
`collection`	An SPL collection that stores elements created from the evaluated `return_expression`. Accepts a single-field collection, a record-type collection, or multiple single-field collections. The number of return expressions must match the total number of fields across all specified collections in order, with compatible types.

Examples

Delete all rows for employee 7900 from the jobhist table:

DELETE FROM jobhist WHERE empno = 7900;

Remove all rows from the jobhist table:

DELETE FROM jobhist;

Delete all rows from the result set of a subquery:

DELETE FROM (SELECT * FROM t);