Compute
Elastic Cloud Server
Huawei Cloud Flexus
Bare Metal Server
Auto Scaling
Image Management Service
Dedicated Host
FunctionGraph
Cloud Phone Host
Huawei Cloud EulerOS
Networking
Virtual Private Cloud
Elastic IP
Elastic Load Balance
NAT Gateway
Direct Connect
Virtual Private Network
VPC Endpoint
Cloud Connect
Enterprise Router
Enterprise Switch
Global Accelerator
Management & Governance
Cloud Eye
Identity and Access Management
Cloud Trace Service
Resource Formation Service
Tag Management Service
Log Tank Service
Config
OneAccess
Resource Access Manager
Simple Message Notification
Application Performance Management
Application Operations Management
Organizations
Optimization Advisor
IAM Identity Center
Cloud Operations Center
Resource Governance Center
Migration
Server Migration Service
Object Storage Migration Service
Cloud Data Migration
Migration Center
Cloud Ecosystem
KooGallery
Partner Center
User Support
My Account
Billing Center
Cost Center
Resource Center
Enterprise Management
Service Tickets
HUAWEI CLOUD (International) FAQs
ICP Filing
Support Plans
My Credentials
Customer Operation Capabilities
Partner Support Plans
Professional Services
Analytics
MapReduce Service
Data Lake Insight
CloudTable Service
Cloud Search Service
Data Lake Visualization
Data Ingestion Service
GaussDB(DWS)
DataArts Studio
Data Lake Factory
DataArts Lake Formation
IoT
IoT Device Access
Others
Product Pricing Details
System Permissions
Console Quick Start
Common FAQs
Instructions for Associating with a HUAWEI CLOUD Partner
Message Center
Security & Compliance
Security Technologies and Applications
Web Application Firewall
Host Security Service
Cloud Firewall
SecMaster
Anti-DDoS Service
Data Encryption Workshop
Database Security Service
Cloud Bastion Host
Data Security Center
Cloud Certificate Manager
Edge Security
Managed Threat Detection
Blockchain
Blockchain Service
Web3 Node Engine Service
Media Services
Media Processing Center
Video On Demand
Live
SparkRTC
MetaStudio
Storage
Object Storage Service
Elastic Volume Service
Cloud Backup and Recovery
Storage Disaster Recovery Service
Scalable File Service Turbo
Scalable File Service
Volume Backup Service
Cloud Server Backup Service
Data Express Service
Dedicated Distributed Storage Service
Containers
Cloud Container Engine
SoftWare Repository for Container
Application Service Mesh
Ubiquitous Cloud Native Service
Cloud Container Instance
Databases
Relational Database Service
Document Database Service
Data Admin Service
Data Replication Service
GeminiDB
GaussDB
Distributed Database Middleware
Database and Application Migration UGO
TaurusDB
Middleware
Distributed Cache Service
API Gateway
Distributed Message Service for Kafka
Distributed Message Service for RabbitMQ
Distributed Message Service for RocketMQ
Cloud Service Engine
Multi-Site High Availability Service
EventGrid
Dedicated Cloud
Dedicated Computing Cluster
Business Applications
Workspace
ROMA Connect
Message & SMS
Domain Name Service
Edge Data Center Management
Meeting
AI
Face Recognition Service
Graph Engine Service
Content Moderation
Image Recognition
Optical Character Recognition
ModelArts
ImageSearch
Conversational Bot Service
Speech Interaction Service
Huawei HiLens
Video Intelligent Analysis Service
Developer Tools
SDK Developer Guide
API Request Signing Guide
Terraform
Koo Command Line Interface
Content Delivery & Edge Computing
Content Delivery Network
Intelligent EdgeFabric
CloudPond
Intelligent EdgeCloud
Solutions
SAP Cloud
High Performance Computing
Developer Services
ServiceStage
CodeArts
CodeArts PerfTest
CodeArts Req
CodeArts Pipeline
CodeArts Build
CodeArts Deploy
CodeArts Artifact
CodeArts TestPlan
CodeArts Check
CodeArts Repo
Cloud Application Engine
MacroVerse aPaaS
KooMessage
KooPhone
KooDrive

DELETE

Updated on 2024-08-20 GMT+08:00

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 ] } [, ...] } ];

Delete multiple tables:

[ WITH [ RECURSIVE ] with_query [, ...] ]
DELETE [/*+ plan_hint */] [FROM] 
    {[ ONLY ] table_name [ * ] [ [ [partition_clause]  [ [ AS ] alias ] ] | [ [ [ AS ] alias ] [partitions_clause] ] ]} [, ...]
    [ USING using_list ]
    [ WHERE condition ];

or

[ WITH [ RECURSIVE ] with_query [, ...] ]
DELETE [/*+ plan_hint */]
    {[ ONLY ] table_name [ * ] [ [ [partition_clause]  [ [ AS ] alias ] ] | [ [ [ AS ] alias ] [partitions_clause] ] ]} [, ...]
    [ FROM using_list ]
    [ WHERE condition ];

Format of with_query:

with_query_name [ ( column_name [, ...] ) ] AS [ [ NOT ] MATERIALIZED ]
( {select | values | insert | update | delete} )

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

    NOTE:

    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.

    NOTE:

    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.

    NOTICE:

    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.

We use cookies to improve our site and your experience. By continuing to browse our site you accept our cookie policy. Find out more

Feedback

Feedback

Feedback

0/500

Selected Content

Submit selected content with the feedback