DELETE

功能描述

DELETE从指定的表里删除满足WHERE子句的行。如果WHERE子句不存在，将删除表中所有行，结果只保留表结构。

注意事项

  • 本章节仅包含shark新增语法，原openGauss的DELETE语法未作删除和修改。原openGauss的DELETE语法请参考章节DELETE。
  • 新增支持table_hint子句。

语法格式

1. 单表删除：
2. [ WITH [ RECURSIVE ] with_query [, ...] ]
3. DELETE [/*+ plan_hint */] [FROM] [ ONLY ] table_name [ * ]
4. [ [ [partition_clause] [ [ AS ] alias ] [ table_hint_clause ] ] | [ [ [ AS ] alias ] [partitions_clause] ] ]
5. [ USING using_list ]
6. [ FROM from_list
7. [JOIN join_table ON join_condition]... ]
8. [ WHERE condition | WHERE CURRENT OF cursor_name ]
9. [ ORDER BY {expression [ [ ASC | DESC | USING operator ]
10. [ LIMIT { count } ]
11. [ RETURNING { * | { output_expr [ [ AS ] output_name ] } [, ...] } ];

13. 多表删除：
14. [ WITH [ RECURSIVE ] with_query [, ...] ]
15. DELETE [/*+ plan_hint */] [FROM]
16. {[ ONLY ] table_name [ * ] [ [ [partition_clause] [ [ AS ] alias ] [ table_hint_clause ] ] | [ [ [ AS ] alias ] [partitions_clause] ] ]} [, ...]
17. [ USING using_list ]
18. [ FROM from_list
19. [JOIN join_table ON join_condition]... ]
20. [ WHERE condition  ];
21. 或
22. [ WITH [ RECURSIVE ] with_query [, ...] ]
23. DELETE [/*+ plan_hint */]
24. {[ ONLY ] table_name [ * ] [ [ [partition_clause] [ [ AS ] alias ] [ table_hint_clause ] ] | [ [ [ AS ] alias ] [partitions_clause] ] ]} [, ...]
25. [ FROM using_list ]
26. [ WHERE condition ];
  • 其中table_hint子句table_hint_clause为：

1. 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子句示例

1. create table t1 (c1 int);

3. delete from t1 with (nolock) where c1 = 5;
4. NOTICE:  The nolock option is currently ignored

6. delete from t1 with (nolock, nowait) where c1 = 5;
7. NOTICE:  The nolock option is currently ignored
8. NOTICE:  The nowait option is currently ignored

10. delete from t1 with (nolock nowait) where c1 = 5;
11. NOTICE:  The nolock option is currently ignored
12. NOTICE:  The nowait option is currently ignored

相关链接

DELETE