DELETE
功能描述
DELETE从指定的表里删除满足WHERE子句的行。如果WHERE子句不存在，将删除表中所有行，结果只保留表结构。
注意事项
- 本章节仅包含shark新增语法，原openGauss的DELETE语法未作删除和修改。原openGauss的DELETE语法请参考章节DELETE。
- 新增支持table_hint子句。
语法格式
单表删除：
[ WITH [ RECURSIVE ] with_query [, ...] ]
DELETE [/*+ plan_hint */] [FROM] [ ONLY ] table_name [ * ]
[ [ [partition_clause] [ [ AS ] alias ] [ table_hint_clause ] ] | [ [ [ AS ] alias ] [partitions_clause] ] ]
[ USING using_list ]
[ FROM from_list
[JOIN join_table ON join_condition]... ]
[ WHERE condition | WHERE CURRENT OF cursor_name ]
[ ORDER BY {expression [ [ ASC | DESC | USING operator ]
[ LIMIT { count } ]
[ RETURNING { * | { output_expr [ [ AS ] output_name ] } [, ...] } ];
多表删除：
[ WITH [ RECURSIVE ] with_query [, ...] ]
DELETE [/*+ plan_hint */] [FROM]
{[ ONLY ] table_name [ * ] [ [ [partition_clause] [ [ AS ] alias ] [ table_hint_clause ] ] | [ [ [ AS ] alias ] [partitions_clause] ] ]} [, ...]
[ USING using_list ]
[ FROM from_list
[JOIN join_table ON join_condition]... ]
[ WHERE condition ];
或
[ WITH [ RECURSIVE ] with_query [, ...] ]
DELETE [/*+ plan_hint */]
{[ ONLY ] table_name [ * ] [ [ [partition_clause] [ [ AS ] alias ] [ table_hint_clause ] ] | [ [ [ AS ] alias ] [partitions_clause] ] ]} [, ...]
[ FROM using_list ]
[ WHERE condition ];其中table_hint子句table_hint_clause为：
WITH ( <table_hint> [, ...] )
参数说明
JOIN
JOIN包含 INNER JOIN，LEFT JOIN，RIGHT JOIN，FULL JOIN，CROSS JOIN。
WITH ( <table_hint> [, ...] )
不同于SELECT子句，针对单个hint，WITH可选，针对DELETE子句，WITH必选，table_hint支持给出一个列表选项，列表通过逗号或者空格分隔，即WITH (hint1)、WITH (hint1, hint2, ...)、WITH (hint1 hint2 ...)均支持，(hint1)不支持。
支持的hint包括NOLOCK、READUNCOMMITTED、UPDLOCK、REPEATABLEREAD、SERIALIZABLE、READCOMMITTED、TABLOCK、TABLOCKX、PAGLOCK、ROWLOCK、NOWAIT、READPAST、XLOCK、SNAPSHOT、NOEXPAND。
当上述hint需要当做标识符，用于列名、变量名等，需要设置d_format_behavior_compat_options = 'enable_table_hint_identifier'，该变量默认值d_format_behavior_compat_options = ''。
所有的hint仅语法支持，无实际含义。
针对hint，会打印相关NOTICE信息。
table_hint子句示例
sql
openGauss=# create database testd dbcompatibility = 'D';
CREATE DATABASE
openGauss=# \c testd
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "testd" as user "omm".
testd=# create extension shark;
CREATE EXTENSION
testd=# create table t1 (c1 int);
CREATE TABLE
testd=# delete from t1 with (nolock) where c1 = 5;
NOTICE: The nolock option is currently ignored
DELETE 0
testd=# delete from t1 with (nolock, nowait) where c1 = 5;
NOTICE: The nolock option is currently ignored
NOTICE: The nowait option is currently ignored
DELETE 0
testd=# delete from t1 with (nolock nowait) where c1 = 5;
NOTICE: The nolock option is currently ignored
NOTICE: The nowait option is currently ignored
DELETE 0
testd=# drop table t1;
DROP TABLE