{"Entry":{"collection":"sql","key":"delete","name":"DELETE","aliases":["delete"],"metadata":{"aliases":["delete"],"changed_in":["7.1","8.1","8.2","8.3","8.4","9.0","9.1","9.2","9.5","12","18"],"changes":[{"from":"6.4","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["description"],"removed":[]},"status":"changed","synopsis":null,"to":"6.5"},{"from":"6.5","purpose_changed":true,"renamed":null,"sections":{"added":[],"changed":["description","usage"],"removed":[]},"status":"changed","synopsis":null,"to":"7.0"},{"from":"7.0","purpose_changed":false,"renamed":{"from_file":"sql-delete.htm","to_file":"sql-delete.html"},"sections":{"added":[],"changed":["description"],"removed":[]},"status":"changed","synopsis":{"added":["DELETE FROM [ ONLY ] table [ WHERE condition ]"],"removed":[]},"to":"7.1"},{"from":"7.1","purpose_changed":true,"renamed":null,"sections":{"added":[],"changed":["description","compatibility"],"removed":[]},"status":"changed","synopsis":null,"to":"7.2"},{"from":"7.3","purpose_changed":false,"renamed":null,"sections":{"added":["parameters","outputs","examples"],"changed":["description","compatibility"],"removed":["usage"]},"status":"changed","synopsis":null,"to":"7.4"},{"from":"7.4","purpose_changed":false,"renamed":null,"sections":{"added":["notes"],"changed":["description","compatibility"],"removed":[]},"status":"changed","synopsis":null,"to":"8.0"},{"from":"8.0","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["description","parameters","notes","compatibility"],"removed":[]},"status":"changed","synopsis":{"added":["[ USING usinglist ]","[ WHERE condition ]"],"removed":[]},"to":"8.1"},{"from":"8.1","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["description","parameters","outputs","examples","compatibility"],"removed":[]},"status":"changed","synopsis":{"added":["DELETE FROM [ ONLY ] table [ [ AS ] alias ]","[ RETURNING * | output_expression [ AS output_name ] [, ...] ]"],"removed":[]},"to":"8.2"},{"from":"8.2","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["description","parameters","notes","examples"],"removed":[]},"status":"changed","synopsis":{"added":["DELETE FROM [ ONLY ] table [ * ] [ [ AS ] alias ]","[ WHERE condition | WHERE CURRENT OF cursor_name ]"],"removed":[]},"to":"8.3"},{"from":"8.3","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["parameters"],"removed":[]},"status":"changed","synopsis":{"added":["[ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ]"],"removed":[]},"to":"8.4"},{"from":"8.4","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["parameters"],"removed":[]},"status":"changed","synopsis":{"added":["[ USING using_list ]"],"removed":["[ USING usinglist ]"]},"to":"9.0"},{"from":"9.0","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["parameters","compatibility"],"removed":[]},"status":"changed","synopsis":{"added":["[ WITH [ RECURSIVE ] with_query [, ...] ]"],"removed":[]},"to":"9.1"},{"from":"9.1","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["parameters","outputs"],"removed":[]},"status":"changed","synopsis":{"added":["DELETE FROM [ ONLY ] table_name [ * ] [ [ AS ] alias ]"],"removed":["DELETE FROM [ ONLY ] table [ * ] [ [ AS ] alias ]"]},"to":"9.2"},{"from":"9.4","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["parameters"],"removed":[]},"status":"changed","synopsis":{"added":["[ USING from_item [, ...] ]"],"removed":["[ USING using_list ]"]},"to":"9.5"},{"from":"9.6","purpose_changed":false,"renamed":null,"sections":{"added":["see_also"],"changed":["description"],"removed":[]},"status":"changed","synopsis":null,"to":"10"},{"from":"11","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":[],"removed":[]},"status":"changed","synopsis":{"added":["[ RETURNING { * | output_expression [ [ AS ] output_name ] } [, ...] ]"],"removed":[]},"to":"12"},{"from":"12","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["parameters"],"removed":[]},"status":"changed","synopsis":null,"to":"13"},{"from":"16","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["examples"],"removed":[]},"status":"changed","synopsis":null,"to":"17"},{"from":"17","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["parameters"],"removed":[]},"status":"changed","synopsis":{"added":["[ RETURNING [ WITH ( { OLD | NEW } AS output_alias [, ...] ) ]"],"removed":[]},"to":"18"}],"content_hash":"8cce62c7ca13f16c36ac65ddc3f59dbbe6461692822051ea04ed9fc5d4ee607a","derived_content_hash":"e990efca1ce2576c492a09e2d7fc89185b7c4bb9ac1c08e523f298ff1807392f","editorial":{},"first_version":"6.4","group":"query","imported_at":"2026-09-30T17:43:38.998996+08:00","last_version":"20","name":"DELETE","object":"","position":11001,"present_in":["6.4","6.5","7.0","7.1","7.2","7.3","7.4","8.0","8.1","8.2","8.3","8.4","9.0","9.1","9.2","9.3","9.4","9.5","9.6","10","11","12","13","14","15","16","17","18","19","20"],"purpose":"delete rows of a table","purpose_zh":"","related":["truncate"],"repair_provenance":{"original_source_revision":"wiki-repair-20261023:cd8ca132e1d89816918c3a2a7d103d8d3fe7f952b90069599fda94e769036ef2","receipt_sha256":"9593373dc6aaa542267c41bb17cf84801a6d5388eba9c015286add9e94357935","repair":"wiki-repair-20261023"},"slug":"delete","source_rev":"wiki-repair-20261023:4de4e42ec2301dfa3e8b13684f2742a468c12b9beac552c8890d34850989f958","synopsis":"[ WITH [ RECURSIVE ] with_query [, ...] ]\nDELETE FROM [ ONLY ] table_name [ * ] [ [ AS ] alias ]\n[ USING from_item [, ...] ]\n[ WHERE condition | WHERE CURRENT OF cursor_name ]\n[ RETURNING [ WITH ( { OLD | NEW } AS output_alias [, ...] ) ]\n{ * | output_expression [ [ AS ] output_name ] } [, ...] ]","verb":"DELETE"}},"Definition":{"Collection":"sql","Key":"delete","SourceDatabase":"center","Version":"18","SourceTable":"sqlcmd","SourceKey":"delete","SourceRevision":"wiki-repair-20261023:4de4e42ec2301dfa3e8b13684f2742a468c12b9beac552c8890d34850989f958","Facts":{"anchor":"SQL-DELETE","description_zh":"删除表中的行","description_zh_html":"\u003cpre\u003e[ WITH [ RECURSIVE ] with_query [, ...] ]\nDELETE FROM [ ONLY ] table_name [ * ] [ [ AS ] alias ]\n[ USING from_item [, ...] ]\n[ WHERE condition | WHERE CURRENT OF cursor_name ]\n[ RETURNING [ WITH ( { OLD | NEW } AS output_alias [, ...] ) ]\n{ * | output_expression [ [ AS ] output_name ] } [, ...] ]\u003c/pre\u003e\u003csection\u003e\u003ch2\u003e描述\u003c/h2\u003e\u003cp\u003e\n\u003ccode class=\"command\"\u003eDELETE\u003c/code\u003e 从指定表中删除满足\n   \u003ccode class=\"literal\"\u003eWHERE\u003c/code\u003e 子句的行。如果省略 \u003ccode class=\"literal\"\u003eWHERE\u003c/code\u003e\n   子句，效果就是删除表中的所有行。结果是一个合法但为空的表。\n  \u003c/p\u003e\u003cdiv class=\"tip\"\u003e\u003ch3 class=\"title\"\u003e提示\u003c/h3\u003e\u003cp\u003e\n\u003ca class=\"link\" href=\"/zh/docs/18/sql-truncate.html\" title=\"TRUNCATE\"\u003e\u003ccode class=\"command\"\u003eTRUNCATE\u003c/code\u003e\u003c/a\u003e 提供了一种更快的机制来移除表中的所有行。\n    \u003c/p\u003e\u003c/div\u003e\u003cp\u003e\n   有两种方法可以利用数据库中其他表所包含的信息来删除一个表中的行：使用子选择，或者在 \u003ccode class=\"literal\"\u003eUSING\u003c/code\u003e 子句中指定附加表。哪种技术更合适取决于具体情况。\n  \u003c/p\u003e\u003cp\u003e\n   可选的 \u003ccode class=\"literal\"\u003eRETURNING\u003c/code\u003e 子句使 \u003ccode class=\"command\"\u003eDELETE\u003c/code\u003e\n   基于每个实际被删除的行计算并返回一个或多个值。可以计算使用该表列和/或 \u003ccode class=\"literal\"\u003eUSING\u003c/code\u003e 中提到的其他表列的任意表达式。\u003ccode class=\"literal\"\u003eRETURNING\u003c/code\u003e 列表的语法与 \u003ccode class=\"command\"\u003eSELECT\u003c/code\u003e\n   的输出列表相同。\n  \u003c/p\u003e\u003cp\u003e\n   要从一个表中删除行，必须具有该表上的 \u003ccode class=\"literal\"\u003eDELETE\u003c/code\u003e 权限，以及 \u003ccode class=\"literal\"\u003eUSING\u003c/code\u003e 子句中出现的任何表或者其值会在\n   \u003cem class=\"replaceable\"\u003e\u003ccode\u003econdition\u003c/code\u003e\u003c/em\u003e 中读取的任何表上的 \u003ccode class=\"literal\"\u003eSELECT\u003c/code\u003e 权限。\n  \u003c/p\u003e\u003c/section\u003e\u003csection\u003e\u003ch2\u003e参数\u003c/h2\u003e\u003cdiv class=\"variablelist\"\u003e\u003cdl class=\"variablelist\"\u003e\u003cdt id=\"ZH18-AUTO-DELETE-VARLISTENTRY-1\"\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003ewith_query\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e \u003c/dt\u003e\u003cdd\u003e\u003cp\u003e\n\u003ccode class=\"literal\"\u003eWITH\u003c/code\u003e 子句允许你指定一个或多个子查询，这些子查询可在 \u003ccode class=\"command\"\u003eDELETE\u003c/code\u003e 查询中按名称引用。详见\u003ca class=\"xref\" href=\"/zh/docs/18/queries-with.html\" title=\"7.8. WITH查询（公共表表达式）\"\u003e第 7.8 节\u003c/a\u003e和 \u003ca class=\"xref\" href=\"/zh/docs/18/sql-select.html\" title=\"SELECT\"\u003e\u003cspan class=\"refentrytitle\"\u003eSELECT\u003c/span\u003e\u003c/a\u003e。\n     \u003c/p\u003e\u003c/dd\u003e\u003cdt id=\"ZH18-AUTO-DELETE-VARLISTENTRY-2\"\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003etable_name\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e \u003c/dt\u003e\u003cdd\u003e\u003cp\u003e\n      要从中删除行的表名（可以是模式限定的）。如果在表名前指定了\n      \u003ccode class=\"literal\"\u003eONLY\u003c/code\u003e，只会从所提及表中删除匹配行。如果未指定\n      \u003ccode class=\"literal\"\u003eONLY\u003c/code\u003e，还会删除继承自该表的任何表中的匹配行。可选地，可以在表名后指定\u003ccode class=\"literal\"\u003e*\u003c/code\u003e，以显式指示包含后代表。\n     \u003c/p\u003e\u003c/dd\u003e\u003cdt id=\"ZH18-AUTO-DELETE-VARLISTENTRY-3\"\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003ealias\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e \u003c/dt\u003e\u003cdd\u003e\u003cp\u003e\n      目标表的替代名称。提供别名时，它会完全隐藏该表的实际名称。例如，给定 \u003ccode class=\"literal\"\u003eDELETE FROM foo AS f\u003c/code\u003e，\u003ccode class=\"command\"\u003eDELETE\u003c/code\u003e 语句的其余部分必须将该表称为\n      \u003ccode class=\"literal\"\u003ef\u003c/code\u003e，而不是 \u003ccode class=\"literal\"\u003efoo\u003c/code\u003e。\n     \u003c/p\u003e\u003c/dd\u003e\u003cdt id=\"ZH18-AUTO-DELETE-VARLISTENTRY-4\"\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003efrom_item\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e \u003c/dt\u003e\u003cdd\u003e\u003cp\u003e\n      一个表表达式，允许其他表的列出现在 \u003ccode class=\"literal\"\u003eWHERE\u003c/code\u003e 条件中。这里使用的语法与 \u003ccode class=\"command\"\u003eSELECT\u003c/code\u003e 语句的\n      \u003ca class=\"link\" href=\"/zh/docs/18/sql-select.html#SQL-FROM\" title=\"FROM 子句\"\u003e\u003ccode class=\"literal\"\u003eFROM\u003c/code\u003e\u003c/a\u003e 子句相同；例如，可以为表名指定别名。除非你打算进行自连接（这种情况下它必须在 \u003cem class=\"replaceable\"\u003e\u003ccode\u003efrom_item\u003c/code\u003e\u003c/em\u003e 中带有别名出现），否则不要把目标表重复写成 \u003cem class=\"replaceable\"\u003e\u003ccode\u003efrom_item\u003c/code\u003e\u003c/em\u003e。\n     \u003c/p\u003e\u003c/dd\u003e\u003cdt id=\"ZH18-AUTO-DELETE-VARLISTENTRY-5\"\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003econdition\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e \u003c/dt\u003e\u003cdd\u003e\u003cp\u003e\n      一个返回 \u003ccode class=\"type\"\u003eboolean\u003c/code\u003e 类型值的表达式。只有使这个表达式返回 \u003ccode class=\"literal\"\u003etrue\u003c/code\u003e 的行才会被删除。\n     \u003c/p\u003e\u003c/dd\u003e\u003cdt id=\"ZH18-AUTO-DELETE-VARLISTENTRY-6\"\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003ecursor_name\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e \u003c/dt\u003e\u003cdd\u003e\u003cp\u003e\n      要在 \u003ccode class=\"literal\"\u003eWHERE CURRENT OF\u003c/code\u003e 条件中使用的游标名。要被删除的行是最近一次从该游标中取出的那一行。该游标必须是针对 \u003ccode class=\"command\"\u003eDELETE\u003c/code\u003e 目标表的非分组查询。注意，\u003ccode class=\"literal\"\u003eWHERE CURRENT OF\u003c/code\u003e 不能与布尔条件同时指定。有关将游标用于 \u003ccode class=\"literal\"\u003eWHERE CURRENT OF\u003c/code\u003e 的更多信息，请见 \u003ca class=\"xref\" href=\"/zh/docs/18/sql-declare.html\" title=\"DECLARE\"\u003e\u003cspan class=\"refentrytitle\"\u003eDECLARE\u003c/span\u003e\u003c/a\u003e。\n     \u003c/p\u003e\u003c/dd\u003e\u003cdt id=\"ZH18-AUTO-DELETE-VARLISTENTRY-7\"\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003eoutput_alias\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e \u003c/dt\u003e\u003cdd\u003e\u003cp\u003e\n      在 \u003ccode class=\"literal\"\u003eRETURNING\u003c/code\u003e 列表中为 \u003ccode class=\"literal\"\u003eOLD\u003c/code\u003e 或\n      \u003ccode class=\"literal\"\u003eNEW\u003c/code\u003e 行指定的可选替代名称。\n     \u003c/p\u003e\u003cp\u003e\n        默认情况下，可通过\n        \u003ccode class=\"literal\"\u003eOLD.\u003cem class=\"replaceable\"\u003e\u003ccode\u003ecolumn_name\u003c/code\u003e\u003c/em\u003e\u003c/code\u003e\n        或 \u003ccode class=\"literal\"\u003eOLD.*\u003c/code\u003e 返回目标表旧值，通过\n        \u003ccode class=\"literal\"\u003eNEW.\u003cem class=\"replaceable\"\u003e\u003ccode\u003ecolumn_name\u003c/code\u003e\u003c/em\u003e\u003c/code\u003e\n        或 \u003ccode class=\"literal\"\u003eNEW.*\u003c/code\u003e 返回新值。提供别名后，这些名称会被隐藏，必须使用别名来引用旧行或新行。例如\n        \u003ccode class=\"literal\"\u003eRETURNING WITH (OLD AS o, NEW AS n) o.*, n.*\u003c/code\u003e。\n       \u003c/p\u003e\u003c/dd\u003e\u003cdt id=\"ZH18-AUTO-DELETE-VARLISTENTRY-8\"\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003eoutput_expression\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e \u003c/dt\u003e\u003cdd\u003e\u003cp\u003e\n      在每一行被删除后，由 \u003ccode class=\"command\"\u003eDELETE\u003c/code\u003e 命令计算并返回的表达式。该表达式可以使用由 \u003cem class=\"replaceable\"\u003e\u003ccode\u003etable_name\u003c/code\u003e\u003c/em\u003e\n      命名的表或 \u003ccode class=\"literal\"\u003eUSING\u003c/code\u003e 中列出的表的任何列名。写成\n      \u003ccode class=\"literal\"\u003e*\u003c/code\u003e 可返回所有列。\n     \u003c/p\u003e\u003cp\u003e\n      列名或\u003ccode class=\"literal\"\u003e*\u003c/code\u003e 可以用 \u003ccode class=\"literal\"\u003eOLD\u003c/code\u003e 或\n      \u003ccode class=\"literal\"\u003eNEW\u003c/code\u003e 进行限定，也可以用 \u003ccode class=\"literal\"\u003eOLD\u003c/code\u003e 或 \u003ccode class=\"literal\"\u003eNEW\u003c/code\u003e 对应的\n      \u003cem class=\"replaceable\"\u003e\u003ccode\u003eoutput_alias\u003c/code\u003e\u003c/em\u003e 进行限定，以返回旧值或新值。未限定的列名或\u003ccode class=\"literal\"\u003e*\u003c/code\u003e，以及用目标表名称或别名限定的列名或\u003ccode class=\"literal\"\u003e*\u003c/code\u003e，都会返回旧值。\n     \u003c/p\u003e\u003cp\u003e\n      对于简单的 \u003ccode class=\"command\"\u003eDELETE\u003c/code\u003e，所有新值都将为\n      \u003ccode class=\"literal\"\u003eNULL\u003c/code\u003e。但是，如果某个 \u003ccode class=\"literal\"\u003eON DELETE\u003c/code\u003e\n      规则导致转而执行 \u003ccode class=\"command\"\u003eINSERT\u003c/code\u003e 或 \u003ccode class=\"command\"\u003eUPDATE\u003c/code\u003e，那么新值可能为非 \u003ccode class=\"literal\"\u003eNULL\u003c/code\u003e。\n     \u003c/p\u003e\u003c/dd\u003e\u003cdt id=\"ZH18-AUTO-DELETE-VARLISTENTRY-9\"\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003eoutput_name\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e \u003c/dt\u003e\u003cdd\u003e\u003cp\u003e\n      用于返回列的名称。\n     \u003c/p\u003e\u003c/dd\u003e\u003c/dl\u003e\u003c/div\u003e\u003c/section\u003e\u003csection\u003e\u003ch2\u003e输出\u003c/h2\u003e\u003cp\u003e\n   在成功完成时，一个 \u003ccode class=\"command\"\u003eDELETE\u003c/code\u003e 命令会返回以下形式的命令标签：\n\u003c/p\u003e\u003cpre class=\"screen\"\u003e\nDELETE \u003cem class=\"replaceable\"\u003e\u003ccode\u003ecount\u003c/code\u003e\u003c/em\u003e\n\u003c/pre\u003e\u003cp\u003e\n\u003cem class=\"replaceable\"\u003e\u003ccode\u003ecount\u003c/code\u003e\u003c/em\u003e 是被删除的行数。注意，当删除被 \u003ccode class=\"literal\"\u003eBEFORE DELETE\u003c/code\u003e 触发器抑制时，这个数量可能小于匹配 \u003cem class=\"replaceable\"\u003e\u003ccode\u003econdition\u003c/code\u003e\u003c/em\u003e\n   的行数。如果 \u003cem class=\"replaceable\"\u003e\u003ccode\u003ecount\u003c/code\u003e\u003c/em\u003e 为 0，则该查询没有删除任何行（这不被视为错误）。\n  \u003c/p\u003e\u003cp\u003e\n   如果 \u003ccode class=\"command\"\u003eDELETE\u003c/code\u003e 命令包含 \u003ccode class=\"literal\"\u003eRETURNING\u003c/code\u003e\n   子句，其结果将类似于一个 \u003ccode class=\"command\"\u003eSELECT\u003c/code\u003e 语句，其中包含\n   \u003ccode class=\"literal\"\u003eRETURNING\u003c/code\u003e 列表中定义的列和值，并在该命令删除的行上进行计算。\n  \u003c/p\u003e\u003c/section\u003e\u003csection\u003e\u003ch2\u003e注解\u003c/h2\u003e\u003cp\u003e\n\u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e 允许通过在\n   \u003ccode class=\"literal\"\u003eUSING\u003c/code\u003e 子句中指定其他表，在\n   \u003ccode class=\"literal\"\u003eWHERE\u003c/code\u003e 条件中引用这些表的列。例如，要删除由给定制片人制作的所有电影，可以这样做：\n\u003c/p\u003e\u003cpre class=\"programlisting\"\u003e\nDELETE FROM films USING producers\n  WHERE producer_id = producers.id AND producers.name = 'foo';\n\u003c/pre\u003e\u003cp\u003e\n   本质上，这里发生的是在 \u003ccode class=\"structname\"\u003efilms\u003c/code\u003e 和\n   \u003ccode class=\"structname\"\u003eproducers\u003c/code\u003e 之间进行连接，并将所有成功连接到的 \u003ccode class=\"structname\"\u003efilms\u003c/code\u003e 行标记为删除。这种语法不是标准的。更标准的写法是：\n\u003c/p\u003e\u003cpre class=\"programlisting\"\u003e\nDELETE FROM films\n  WHERE producer_id IN (SELECT id FROM producers WHERE name = 'foo');\n\u003c/pre\u003e\u003cp\u003e\n   在某些情况下，连接形式比子选择形式更容易书写或者执行更快。\n  \u003c/p\u003e\u003c/section\u003e\u003csection\u003e\u003ch2\u003e示例\u003c/h2\u003e\u003cp\u003e\n   删除所有电影，但音乐剧除外：\n\u003c/p\u003e\u003cpre class=\"programlisting\"\u003e\nDELETE FROM films WHERE kind \u0026lt;\u0026gt; 'Musical';\n\u003c/pre\u003e\u003cp\u003e\n\u003c/p\u003e\u003cp\u003e\n   清空表 \u003ccode class=\"literal\"\u003efilms\u003c/code\u003e：\n\u003c/p\u003e\u003cpre class=\"programlisting\"\u003e\nDELETE FROM films;\n\u003c/pre\u003e\u003cp\u003e\n\u003c/p\u003e\u003cp\u003e\n   删除已完成的任务，并返回被删除行的完整详情：\n\u003c/p\u003e\u003cpre class=\"programlisting\"\u003e\nDELETE FROM tasks WHERE status = 'DONE' RETURNING *;\n\u003c/pre\u003e\u003cp\u003e\n\u003c/p\u003e\u003cp\u003e\n   删除游标 \u003ccode class=\"literal\"\u003ec_tasks\u003c/code\u003e 当前所定位的\n   \u003ccode class=\"structname\"\u003etasks\u003c/code\u003e 行：\n\u003c/p\u003e\u003cpre class=\"programlisting\"\u003e\nDELETE FROM tasks WHERE CURRENT OF c_tasks;\n\u003c/pre\u003e\u003cp\u003e\n\u003c/p\u003e\u003cp\u003e\n   虽然 \u003ccode class=\"command\"\u003eDELETE\u003c/code\u003e 没有 \u003ccode class=\"literal\"\u003eLIMIT\u003c/code\u003e 子句，但可以采用 \u003ca class=\"link\" href=\"/zh/docs/18/sql-update.html#UPDATE-LIMIT\"\u003e\u003ccode class=\"command\"\u003eUPDATE\u003c/code\u003e 文档中介绍的相同方法\u003c/a\u003e来获得类似效果：\n\u003c/p\u003e\u003cpre class=\"programlisting\"\u003e\nWITH delete_batch AS (\n  SELECT l.ctid FROM user_logs AS l\n    WHERE l.status = 'archived'\n    ORDER BY l.creation_date\n    FOR UPDATE\n    LIMIT 10000\n)\nDELETE FROM user_logs AS dl\n  USING delete_batch AS del\n  WHERE dl.ctid = del.ctid;\n\u003c/pre\u003e\u003cp\u003e\n   这里使用 \u003ccode class=\"structfield\"\u003ectid\u003c/code\u003e 之所以安全，仅仅是因为该查询会被重复执行，从而避免了 \u003ccode class=\"structfield\"\u003ectid\u003c/code\u003e 发生变化的问题。\n  \u003c/p\u003e\u003c/section\u003e\u003csection\u003e\u003ch2\u003e兼容性\u003c/h2\u003e\u003cp\u003e\n   这个命令符合 \u003cacronym class=\"acronym\"\u003eSQL\u003c/acronym\u003e 标准，不过\n   \u003ccode class=\"literal\"\u003eUSING\u003c/code\u003e 和 \u003ccode class=\"literal\"\u003eRETURNING\u003c/code\u003e 子句是\n   \u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e 扩展，在\n   \u003ccode class=\"command\"\u003eDELETE\u003c/code\u003e 中使用 \u003ccode class=\"literal\"\u003eWITH\u003c/code\u003e 也是扩展。\n  \u003c/p\u003e\u003c/section\u003e\u003csection\u003e\u003ch2\u003e另见\u003c/h2\u003e\u003cspan class=\"simplelist\"\u003e\u003ca class=\"xref\" href=\"/zh/docs/18/sql-truncate.html\" title=\"TRUNCATE\"\u003e\u003cspan class=\"refentrytitle\"\u003eTRUNCATE\u003c/span\u003e\u003c/a\u003e\u003c/span\u003e\u003c/section\u003e","file":"sql-delete.html","lang":"en","name":"DELETE","purpose":"delete rows of a table","purpose_zh":"删除表中的行","related":["truncate"],"sections":[{"html":"\u003cp\u003e\u003ccode class=\"command\"\u003eDELETE\u003c/code\u003e deletes rows that satisfy the \u003ccode class=\"literal\"\u003eWHERE\u003c/code\u003e clause from the specified table. If the \u003ccode class=\"literal\"\u003eWHERE\u003c/code\u003e clause is absent, the effect is to delete all rows in the table. The result is a valid, but empty table.\u003c/p\u003e\u003cdiv class=\"tip\"\u003e\u003ch3\u003eTip\u003c/h3\u003e\u003cp\u003e\u003ca href=\"/docs/18/sql-truncate.html\" title=\"TRUNCATE\"\u003e\u003ccode class=\"command\"\u003eTRUNCATE\u003c/code\u003e\u003c/a\u003e provides a faster mechanism to remove all rows from a table.\u003c/p\u003e\u003c/div\u003e\u003cp\u003eThere are two ways to delete rows in a table using information contained in other tables in the database: using sub-selects, or specifying additional tables in the \u003ccode class=\"literal\"\u003eUSING\u003c/code\u003e clause. Which technique is more appropriate depends on the specific circumstances.\u003c/p\u003e\u003cp\u003eThe optional \u003ccode class=\"literal\"\u003eRETURNING\u003c/code\u003e clause causes \u003ccode class=\"command\"\u003eDELETE\u003c/code\u003e to compute and return value(s) based on each row actually deleted. Any expression using the table's columns, and/or columns of other tables mentioned in \u003ccode class=\"literal\"\u003eUSING\u003c/code\u003e, can be computed. The syntax of the \u003ccode class=\"literal\"\u003eRETURNING\u003c/code\u003e list is identical to that of the output list of \u003ccode class=\"command\"\u003eSELECT\u003c/code\u003e.\u003c/p\u003e\u003cp\u003eYou must have the \u003ccode class=\"literal\"\u003eDELETE\u003c/code\u003e privilege on the table to delete from it, as well as the \u003ccode class=\"literal\"\u003eSELECT\u003c/code\u003e privilege for any table in the \u003ccode class=\"literal\"\u003eUSING\u003c/code\u003e clause or whose values are read in the \u003cem class=\"replaceable\"\u003e\u003ccode\u003econdition\u003c/code\u003e\u003c/em\u003e.\u003c/p\u003e","key":"description","title":"Description"},{"html":"\u003cdiv class=\"variablelist\"\u003e\u003cdl class=\"variablelist\"\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003ewith_query\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eThe \u003ccode class=\"literal\"\u003eWITH\u003c/code\u003e clause allows you to specify one or more subqueries that can be referenced by name in the \u003ccode class=\"command\"\u003eDELETE\u003c/code\u003e query. See \u003ca href=\"/docs/18/queries-with.html\" title=\"7.8. WITH Queries (Common Table Expressions)\"\u003eSection 7.8\u003c/a\u003e and \u003ca href=\"/docs/18/sql-select.html\" title=\"SELECT\"\u003e\u003cspan class=\"refentrytitle\"\u003eSELECT\u003c/span\u003e\u003c/a\u003e for details.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003etable_name\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eThe name (optionally schema-qualified) of the table to delete rows from. If \u003ccode class=\"literal\"\u003eONLY\u003c/code\u003e is specified before the table name, matching rows are deleted from the named table only. If \u003ccode class=\"literal\"\u003eONLY\u003c/code\u003e is not specified, matching rows are also deleted from any tables inheriting from the named table. Optionally, \u003ccode class=\"literal\"\u003e*\u003c/code\u003e can be specified after the table name to explicitly indicate that descendant tables are included.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003ealias\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eA substitute name for the target table. When an alias is provided, it completely hides the actual name of the table. For example, given \u003ccode class=\"literal\"\u003eDELETE FROM foo AS f\u003c/code\u003e, the remainder of the \u003ccode class=\"command\"\u003eDELETE\u003c/code\u003e statement must refer to this table as \u003ccode class=\"literal\"\u003ef\u003c/code\u003e not \u003ccode class=\"literal\"\u003efoo\u003c/code\u003e.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003efrom_item\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eA table expression allowing columns from other tables to appear in the \u003ccode class=\"literal\"\u003eWHERE\u003c/code\u003e condition. This uses the same syntax as the \u003ca href=\"/docs/18/sql-select.html#SQL-FROM\" title=\"FROM Clause\"\u003e\u003ccode class=\"literal\"\u003eFROM\u003c/code\u003e\u003c/a\u003e clause of a \u003ccode class=\"command\"\u003eSELECT\u003c/code\u003e statement; for example, an alias for the table name can be specified. Do not repeat the target table as a \u003cem class=\"replaceable\"\u003e\u003ccode\u003efrom_item\u003c/code\u003e\u003c/em\u003e unless you wish to set up a self-join (in which case it must appear with an alias in the \u003cem class=\"replaceable\"\u003e\u003ccode\u003efrom_item\u003c/code\u003e\u003c/em\u003e).\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003econdition\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eAn expression that returns a value of type \u003ccode class=\"type\"\u003eboolean\u003c/code\u003e. Only rows for which this expression returns \u003ccode class=\"literal\"\u003etrue\u003c/code\u003e will be deleted.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003ecursor_name\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eThe name of the cursor to use in a \u003ccode class=\"literal\"\u003eWHERE CURRENT OF\u003c/code\u003e condition. The row to be deleted is the one most recently fetched from this cursor. The cursor must be a non-grouping query on the \u003ccode class=\"command\"\u003eDELETE\u003c/code\u003e's target table. Note that \u003ccode class=\"literal\"\u003eWHERE CURRENT OF\u003c/code\u003e cannot be specified together with a Boolean condition. See \u003ca href=\"/docs/18/sql-declare.html\" title=\"DECLARE\"\u003e\u003cspan class=\"refentrytitle\"\u003eDECLARE\u003c/span\u003e\u003c/a\u003e for more information about using cursors with \u003ccode class=\"literal\"\u003eWHERE CURRENT OF\u003c/code\u003e.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003eoutput_alias\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eAn optional substitute name for \u003ccode class=\"literal\"\u003eOLD\u003c/code\u003e or \u003ccode class=\"literal\"\u003eNEW\u003c/code\u003e rows in the \u003ccode class=\"literal\"\u003eRETURNING\u003c/code\u003e list.\u003c/p\u003e\u003cp\u003eBy default, old values from the target table can be returned by writing \u003ccode class=\"literal\"\u003eOLD.\u003cem class=\"replaceable\"\u003e\u003ccode\u003ecolumn_name\u003c/code\u003e\u003c/em\u003e\u003c/code\u003e or \u003ccode class=\"literal\"\u003eOLD.*\u003c/code\u003e, and new values can be returned by writing \u003ccode class=\"literal\"\u003eNEW.\u003cem class=\"replaceable\"\u003e\u003ccode\u003ecolumn_name\u003c/code\u003e\u003c/em\u003e\u003c/code\u003e or \u003ccode class=\"literal\"\u003eNEW.*\u003c/code\u003e. When an alias is provided, these names are hidden and the old or new rows must be referred to using the alias. For example \u003ccode class=\"literal\"\u003eRETURNING WITH (OLD AS o, NEW AS n) o.*, n.*\u003c/code\u003e.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003eoutput_expression\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eAn expression to be computed and returned by the \u003ccode class=\"command\"\u003eDELETE\u003c/code\u003e command after each row is deleted. The expression can use any column names of the table named by \u003cem class=\"replaceable\"\u003e\u003ccode\u003etable_name\u003c/code\u003e\u003c/em\u003e or table(s) listed in \u003ccode class=\"literal\"\u003eUSING\u003c/code\u003e. Write \u003ccode class=\"literal\"\u003e*\u003c/code\u003e to return all columns.\u003c/p\u003e\u003cp\u003eA column name or \u003ccode class=\"literal\"\u003e*\u003c/code\u003e may be qualified using \u003ccode class=\"literal\"\u003eOLD\u003c/code\u003e or \u003ccode class=\"literal\"\u003eNEW\u003c/code\u003e, or the corresponding \u003cem class=\"replaceable\"\u003e\u003ccode\u003eoutput_alias\u003c/code\u003e\u003c/em\u003e for \u003ccode class=\"literal\"\u003eOLD\u003c/code\u003e or \u003ccode class=\"literal\"\u003eNEW\u003c/code\u003e, to cause old or new values to be returned. An unqualified column name, or \u003ccode class=\"literal\"\u003e*\u003c/code\u003e, or a column name or \u003ccode class=\"literal\"\u003e*\u003c/code\u003e qualified using the target table name or alias will return old values.\u003c/p\u003e\u003cp\u003eFor a simple \u003ccode class=\"command\"\u003eDELETE\u003c/code\u003e, all new values will be \u003ccode class=\"literal\"\u003eNULL\u003c/code\u003e. However, if an \u003ccode class=\"literal\"\u003eON DELETE\u003c/code\u003e rule causes an \u003ccode class=\"command\"\u003eINSERT\u003c/code\u003e or \u003ccode class=\"command\"\u003eUPDATE\u003c/code\u003e to be executed instead, the new values may be non-\u003ccode class=\"literal\"\u003eNULL\u003c/code\u003e.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003eoutput_name\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eA name to use for a returned column.\u003c/p\u003e\u003c/dd\u003e\u003c/dl\u003e\u003c/div\u003e","key":"parameters","title":"Parameters"},{"html":"\u003cp\u003eOn successful completion, a \u003ccode class=\"command\"\u003eDELETE\u003c/code\u003e command returns a command tag of the form\u003c/p\u003e\u003cpre class=\"screen\"\u003eDELETE \u003cem class=\"replaceable\"\u003e\u003ccode\u003ecount\u003c/code\u003e\u003c/em\u003e\n\u003c/pre\u003e\u003cp\u003eThe \u003cem class=\"replaceable\"\u003e\u003ccode\u003ecount\u003c/code\u003e\u003c/em\u003e is the number of rows deleted. Note that the number may be less than the number of rows that matched the \u003cem class=\"replaceable\"\u003e\u003ccode\u003econdition\u003c/code\u003e\u003c/em\u003e when deletes were suppressed by a \u003ccode class=\"literal\"\u003eBEFORE DELETE\u003c/code\u003e trigger. If \u003cem class=\"replaceable\"\u003e\u003ccode\u003ecount\u003c/code\u003e\u003c/em\u003e is 0, no rows were deleted by the query (this is not considered an error).\u003c/p\u003e\u003cp\u003eIf the \u003ccode class=\"command\"\u003eDELETE\u003c/code\u003e command contains a \u003ccode class=\"literal\"\u003eRETURNING\u003c/code\u003e clause, the result will be similar to that of a \u003ccode class=\"command\"\u003eSELECT\u003c/code\u003e statement containing the columns and values defined in the \u003ccode class=\"literal\"\u003eRETURNING\u003c/code\u003e list, computed over the row(s) deleted by the command.\u003c/p\u003e","key":"outputs","title":"Outputs"},{"html":"\u003cp\u003e\u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e lets you reference columns of other tables in the \u003ccode class=\"literal\"\u003eWHERE\u003c/code\u003e condition by specifying the other tables in the \u003ccode class=\"literal\"\u003eUSING\u003c/code\u003e clause. For example, to delete all films produced by a given producer, one can do:\u003c/p\u003e\u003cpre class=\"programlisting\"\u003eDELETE FROM films USING producers\n  WHERE producer_id = producers.id AND producers.name = 'foo';\n\u003c/pre\u003e\u003cp\u003eWhat is essentially happening here is a join between \u003ccode class=\"structname\"\u003efilms\u003c/code\u003e and \u003ccode class=\"structname\"\u003eproducers\u003c/code\u003e, with all successfully joined \u003ccode class=\"structname\"\u003efilms\u003c/code\u003e rows being marked for deletion. This syntax is not standard. A more standard way to do it is:\u003c/p\u003e\u003cpre class=\"programlisting\"\u003eDELETE FROM films\n  WHERE producer_id IN (SELECT id FROM producers WHERE name = 'foo');\n\u003c/pre\u003e\u003cp\u003eIn some cases the join style is easier to write or faster to execute than the sub-select style.\u003c/p\u003e","key":"notes","title":"Notes"},{"html":"\u003cp\u003eDelete all films but musicals:\u003c/p\u003e\u003cpre class=\"programlisting\"\u003eDELETE FROM films WHERE kind \u0026lt;\u0026gt; 'Musical';\n\u003c/pre\u003e\u003cp\u003eClear the table \u003ccode class=\"literal\"\u003efilms\u003c/code\u003e:\u003c/p\u003e\u003cpre class=\"programlisting\"\u003eDELETE FROM films;\n\u003c/pre\u003e\u003cp\u003eDelete completed tasks, returning full details of the deleted rows:\u003c/p\u003e\u003cpre class=\"programlisting\"\u003eDELETE FROM tasks WHERE status = 'DONE' RETURNING *;\n\u003c/pre\u003e\u003cp\u003eDelete the row of \u003ccode class=\"structname\"\u003etasks\u003c/code\u003e on which the cursor \u003ccode class=\"literal\"\u003ec_tasks\u003c/code\u003e is currently positioned:\u003c/p\u003e\u003cpre class=\"programlisting\"\u003eDELETE FROM tasks WHERE CURRENT OF c_tasks;\n\u003c/pre\u003e\u003cp\u003eWhile there is no \u003ccode class=\"literal\"\u003eLIMIT\u003c/code\u003e clause for \u003ccode class=\"command\"\u003eDELETE\u003c/code\u003e, it is possible to get a similar effect using the same method described in \u003ca href=\"/docs/18/sql-update.html#UPDATE-LIMIT\"\u003ethe documentation of \u003ccode class=\"command\"\u003eUPDATE\u003c/code\u003e\u003c/a\u003e:\u003c/p\u003e\u003cpre class=\"programlisting\"\u003eWITH delete_batch AS (\n  SELECT l.ctid FROM user_logs AS l\n    WHERE l.status = 'archived'\n    ORDER BY l.creation_date\n    FOR UPDATE\n    LIMIT 10000\n)\nDELETE FROM user_logs AS dl\n  USING delete_batch AS del\n  WHERE dl.ctid = del.ctid;\n\u003c/pre\u003e\u003cp\u003eThis use of \u003ccode class=\"structfield\"\u003ectid\u003c/code\u003e is only safe because the query is repeatedly run, avoiding the problem of changed \u003ccode class=\"structfield\"\u003ectid\u003c/code\u003es.\u003c/p\u003e","key":"examples","title":"Examples"},{"html":"\u003cp\u003eThis command conforms to the \u003cacronym\u003eSQL\u003c/acronym\u003e standard, except that the \u003ccode class=\"literal\"\u003eUSING\u003c/code\u003e and \u003ccode class=\"literal\"\u003eRETURNING\u003c/code\u003e clauses are \u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e extensions, as is the ability to use \u003ccode class=\"literal\"\u003eWITH\u003c/code\u003e with \u003ccode class=\"command\"\u003eDELETE\u003c/code\u003e.\u003c/p\u003e","key":"compatibility","title":"Compatibility"},{"html":"\u003cspan class=\"simplelist\"\u003e\u003ca href=\"/wiki/sql/truncate/?v=18\" title=\"TRUNCATE\"\u003e\u003cspan class=\"refentrytitle\"\u003eTRUNCATE\u003c/span\u003e\u003c/a\u003e\u003c/span\u003e","key":"see_also","title":"See Also"}],"sections_same_as":"","slug":"18","synopsis_html":"[ WITH [ RECURSIVE ] \u003cem class=\"replaceable\"\u003e\u003ccode\u003ewith_query\u003c/code\u003e\u003c/em\u003e [, ...] ]\nDELETE FROM [ ONLY ] \u003cem class=\"replaceable\"\u003e\u003ccode\u003etable_name\u003c/code\u003e\u003c/em\u003e [ * ] [ [ AS ] \u003cem class=\"replaceable\"\u003e\u003ccode\u003ealias\u003c/code\u003e\u003c/em\u003e ]\n    [ USING \u003cem class=\"replaceable\"\u003e\u003ccode\u003efrom_item\u003c/code\u003e\u003c/em\u003e [, ...] ]\n    [ WHERE \u003cem class=\"replaceable\"\u003e\u003ccode\u003econdition\u003c/code\u003e\u003c/em\u003e | WHERE CURRENT OF \u003cem class=\"replaceable\"\u003e\u003ccode\u003ecursor_name\u003c/code\u003e\u003c/em\u003e ]\n    [ RETURNING [ WITH ( { OLD | NEW } AS \u003cem class=\"replaceable\"\u003e\u003ccode\u003eoutput_alias\u003c/code\u003e\u003c/em\u003e [, ...] ) ]\n                { * | \u003cem class=\"replaceable\"\u003e\u003ccode\u003eoutput_expression\u003c/code\u003e\u003c/em\u003e [ [ AS ] \u003cem class=\"replaceable\"\u003e\u003ccode\u003eoutput_name\u003c/code\u003e\u003c/em\u003e ] } [, ...] ]","synopsis_text":"[ WITH [ RECURSIVE ] with_query [, ...] ]\nDELETE FROM [ ONLY ] table_name [ * ] [ [ AS ] alias ]\n[ USING from_item [, ...] ]\n[ WHERE condition | WHERE CURRENT OF cursor_name ]\n[ RETURNING [ WITH ( { OLD | NEW } AS output_alias [, ...] ) ]\n{ * | output_expression [ [ AS ] output_name ] } [, ...] ]","translation_provenance":{"inputs":[{"kind":"current-pgdoc-translation","path":"/Users/vonng/pgsty/pgdoc/zh/18/html/sql-delete.html","sha256":"d9920bcfbae08e1c808c4fc0877835e56ac33574c77593a7765c4563436f7eda"}],"kind":"same-version-pgdoc","locale":"zh-Hans","source_revision":"b1b500221dbe5437cfca004d6b04d3f6e422765d","version":"18"},"zh_from":"same-version-pgdoc"},"ManualEvidence":{},"MeasuredEvidence":{}},"Text":{"Collection":"sql","Key":"delete","SourceDatabase":"center","Version":"18","Locale":"en","Title":"DELETE","Summary":"delete rows of a table","BodyHTML":"\u003cpre\u003e[ WITH [ RECURSIVE ] with_query [, ...] ]\nDELETE FROM [ ONLY ] table_name [ * ] [ [ AS ] alias ]\n[ USING from_item [, ...] ]\n[ WHERE condition | WHERE CURRENT OF cursor_name ]\n[ RETURNING [ WITH ( { OLD | NEW } AS output_alias [, ...] ) ]\n{ * | output_expression [ [ AS ] output_name ] } [, ...] ]\u003c/pre\u003e\u003csection\u003e\u003ch2\u003eDescription\u003c/h2\u003e\u003cp\u003e\u003ccode\u003eDELETE\u003c/code\u003e deletes rows that satisfy the \u003ccode\u003eWHERE\u003c/code\u003e clause from the specified table. If the \u003ccode\u003eWHERE\u003c/code\u003e clause is absent, the effect is to delete all rows in the table. The result is a valid, but empty table.\u003c/p\u003e\u003cdiv\u003e\u003ch3\u003eTip\u003c/h3\u003e\u003cp\u003e\u003ca href=\"/docs/18/sql-truncate.html\" title=\"TRUNCATE\" rel=\"nofollow\"\u003e\u003ccode\u003eTRUNCATE\u003c/code\u003e\u003c/a\u003e provides a faster mechanism to remove all rows from a table.\u003c/p\u003e\u003c/div\u003e\u003cp\u003eThere are two ways to delete rows in a table using information contained in other tables in the database: using sub-selects, or specifying additional tables in the \u003ccode\u003eUSING\u003c/code\u003e clause. Which technique is more appropriate depends on the specific circumstances.\u003c/p\u003e\u003cp\u003eThe optional \u003ccode\u003eRETURNING\u003c/code\u003e clause causes \u003ccode\u003eDELETE\u003c/code\u003e to compute and return value(s) based on each row actually deleted. Any expression using the table\u0026#39;s columns, and/or columns of other tables mentioned in \u003ccode\u003eUSING\u003c/code\u003e, can be computed. The syntax of the \u003ccode\u003eRETURNING\u003c/code\u003e list is identical to that of the output list of \u003ccode\u003eSELECT\u003c/code\u003e.\u003c/p\u003e\u003cp\u003eYou must have the \u003ccode\u003eDELETE\u003c/code\u003e privilege on the table to delete from it, as well as the \u003ccode\u003eSELECT\u003c/code\u003e privilege for any table in the \u003ccode\u003eUSING\u003c/code\u003e clause or whose values are read in the \u003cem\u003e\u003ccode\u003econdition\u003c/code\u003e\u003c/em\u003e.\u003c/p\u003e\u003c/section\u003e\u003csection\u003e\u003ch2\u003eParameters\u003c/h2\u003e\u003cdiv\u003e\u003cdl\u003e\u003cdt\u003e\u003cspan\u003e\u003cem\u003e\u003ccode\u003ewith_query\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eThe \u003ccode\u003eWITH\u003c/code\u003e clause allows you to specify one or more subqueries that can be referenced by name in the \u003ccode\u003eDELETE\u003c/code\u003e query. See \u003ca href=\"/docs/18/queries-with.html\" rel=\"nofollow\"\u003eSection 7.8\u003c/a\u003e and \u003ca href=\"/docs/18/sql-select.html\" title=\"SELECT\" rel=\"nofollow\"\u003e\u003cspan\u003eSELECT\u003c/span\u003e\u003c/a\u003e for details.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003cem\u003e\u003ccode\u003etable_name\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eThe name (optionally schema-qualified) of the table to delete rows from. If \u003ccode\u003eONLY\u003c/code\u003e is specified before the table name, matching rows are deleted from the named table only. If \u003ccode\u003eONLY\u003c/code\u003e is not specified, matching rows are also deleted from any tables inheriting from the named table. Optionally, \u003ccode\u003e*\u003c/code\u003e can be specified after the table name to explicitly indicate that descendant tables are included.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003cem\u003e\u003ccode\u003ealias\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eA substitute name for the target table. When an alias is provided, it completely hides the actual name of the table. For example, given \u003ccode\u003eDELETE FROM foo AS f\u003c/code\u003e, the remainder of the \u003ccode\u003eDELETE\u003c/code\u003e statement must refer to this table as \u003ccode\u003ef\u003c/code\u003e not \u003ccode\u003efoo\u003c/code\u003e.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003cem\u003e\u003ccode\u003efrom_item\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eA table expression allowing columns from other tables to appear in the \u003ccode\u003eWHERE\u003c/code\u003e condition. This uses the same syntax as the \u003ca href=\"/docs/18/sql-select.html#SQL-FROM\" title=\"FROM Clause\" rel=\"nofollow\"\u003e\u003ccode\u003eFROM\u003c/code\u003e\u003c/a\u003e clause of a \u003ccode\u003eSELECT\u003c/code\u003e statement; for example, an alias for the table name can be specified. Do not repeat the target table as a \u003cem\u003e\u003ccode\u003efrom_item\u003c/code\u003e\u003c/em\u003e unless you wish to set up a self-join (in which case it must appear with an alias in the \u003cem\u003e\u003ccode\u003efrom_item\u003c/code\u003e\u003c/em\u003e).\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003cem\u003e\u003ccode\u003econdition\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eAn expression that returns a value of type \u003ccode\u003eboolean\u003c/code\u003e. Only rows for which this expression returns \u003ccode\u003etrue\u003c/code\u003e will be deleted.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003cem\u003e\u003ccode\u003ecursor_name\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eThe name of the cursor to use in a \u003ccode\u003eWHERE CURRENT OF\u003c/code\u003e condition. The row to be deleted is the one most recently fetched from this cursor. The cursor must be a non-grouping query on the \u003ccode\u003eDELETE\u003c/code\u003e\u0026#39;s target table. Note that \u003ccode\u003eWHERE CURRENT OF\u003c/code\u003e cannot be specified together with a Boolean condition. See \u003ca href=\"/docs/18/sql-declare.html\" title=\"DECLARE\" rel=\"nofollow\"\u003e\u003cspan\u003eDECLARE\u003c/span\u003e\u003c/a\u003e for more information about using cursors with \u003ccode\u003eWHERE CURRENT OF\u003c/code\u003e.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003cem\u003e\u003ccode\u003eoutput_alias\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eAn optional substitute name for \u003ccode\u003eOLD\u003c/code\u003e or \u003ccode\u003eNEW\u003c/code\u003e rows in the \u003ccode\u003eRETURNING\u003c/code\u003e list.\u003c/p\u003e\u003cp\u003eBy default, old values from the target table can be returned by writing \u003ccode\u003eOLD.\u003cem\u003e\u003ccode\u003ecolumn_name\u003c/code\u003e\u003c/em\u003e\u003c/code\u003e or \u003ccode\u003eOLD.*\u003c/code\u003e, and new values can be returned by writing \u003ccode\u003eNEW.\u003cem\u003e\u003ccode\u003ecolumn_name\u003c/code\u003e\u003c/em\u003e\u003c/code\u003e or \u003ccode\u003eNEW.*\u003c/code\u003e. When an alias is provided, these names are hidden and the old or new rows must be referred to using the alias. For example \u003ccode\u003eRETURNING WITH (OLD AS o, NEW AS n) o.*, n.*\u003c/code\u003e.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003cem\u003e\u003ccode\u003eoutput_expression\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eAn expression to be computed and returned by the \u003ccode\u003eDELETE\u003c/code\u003e command after each row is deleted. The expression can use any column names of the table named by \u003cem\u003e\u003ccode\u003etable_name\u003c/code\u003e\u003c/em\u003e or table(s) listed in \u003ccode\u003eUSING\u003c/code\u003e. Write \u003ccode\u003e*\u003c/code\u003e to return all columns.\u003c/p\u003e\u003cp\u003eA column name or \u003ccode\u003e*\u003c/code\u003e may be qualified using \u003ccode\u003eOLD\u003c/code\u003e or \u003ccode\u003eNEW\u003c/code\u003e, or the corresponding \u003cem\u003e\u003ccode\u003eoutput_alias\u003c/code\u003e\u003c/em\u003e for \u003ccode\u003eOLD\u003c/code\u003e or \u003ccode\u003eNEW\u003c/code\u003e, to cause old or new values to be returned. An unqualified column name, or \u003ccode\u003e*\u003c/code\u003e, or a column name or \u003ccode\u003e*\u003c/code\u003e qualified using the target table name or alias will return old values.\u003c/p\u003e\u003cp\u003eFor a simple \u003ccode\u003eDELETE\u003c/code\u003e, all new values will be \u003ccode\u003eNULL\u003c/code\u003e. However, if an \u003ccode\u003eON DELETE\u003c/code\u003e rule causes an \u003ccode\u003eINSERT\u003c/code\u003e or \u003ccode\u003eUPDATE\u003c/code\u003e to be executed instead, the new values may be non-\u003ccode\u003eNULL\u003c/code\u003e.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003cem\u003e\u003ccode\u003eoutput_name\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eA name to use for a returned column.\u003c/p\u003e\u003c/dd\u003e\u003c/dl\u003e\u003c/div\u003e\u003c/section\u003e\u003csection\u003e\u003ch2\u003eOutputs\u003c/h2\u003e\u003cp\u003eOn successful completion, a \u003ccode\u003eDELETE\u003c/code\u003e command returns a command tag of the form\u003c/p\u003e\u003cpre\u003eDELETE \u003cem\u003e\u003ccode\u003ecount\u003c/code\u003e\u003c/em\u003e\n\u003c/pre\u003e\u003cp\u003eThe \u003cem\u003e\u003ccode\u003ecount\u003c/code\u003e\u003c/em\u003e is the number of rows deleted. Note that the number may be less than the number of rows that matched the \u003cem\u003e\u003ccode\u003econdition\u003c/code\u003e\u003c/em\u003e when deletes were suppressed by a \u003ccode\u003eBEFORE DELETE\u003c/code\u003e trigger. If \u003cem\u003e\u003ccode\u003ecount\u003c/code\u003e\u003c/em\u003e is 0, no rows were deleted by the query (this is not considered an error).\u003c/p\u003e\u003cp\u003eIf the \u003ccode\u003eDELETE\u003c/code\u003e command contains a \u003ccode\u003eRETURNING\u003c/code\u003e clause, the result will be similar to that of a \u003ccode\u003eSELECT\u003c/code\u003e statement containing the columns and values defined in the \u003ccode\u003eRETURNING\u003c/code\u003e list, computed over the row(s) deleted by the command.\u003c/p\u003e\u003c/section\u003e\u003csection\u003e\u003ch2\u003eNotes\u003c/h2\u003e\u003cp\u003e\u003cspan\u003ePostgreSQL\u003c/span\u003e lets you reference columns of other tables in the \u003ccode\u003eWHERE\u003c/code\u003e condition by specifying the other tables in the \u003ccode\u003eUSING\u003c/code\u003e clause. For example, to delete all films produced by a given producer, one can do:\u003c/p\u003e\u003cpre\u003eDELETE FROM films USING producers\n  WHERE producer_id = producers.id AND producers.name = \u0026#39;foo\u0026#39;;\n\u003c/pre\u003e\u003cp\u003eWhat is essentially happening here is a join between \u003ccode\u003efilms\u003c/code\u003e and \u003ccode\u003eproducers\u003c/code\u003e, with all successfully joined \u003ccode\u003efilms\u003c/code\u003e rows being marked for deletion. This syntax is not standard. A more standard way to do it is:\u003c/p\u003e\u003cpre\u003eDELETE FROM films\n  WHERE producer_id IN (SELECT id FROM producers WHERE name = \u0026#39;foo\u0026#39;);\n\u003c/pre\u003e\u003cp\u003eIn some cases the join style is easier to write or faster to execute than the sub-select style.\u003c/p\u003e\u003c/section\u003e\u003csection\u003e\u003ch2\u003eExamples\u003c/h2\u003e\u003cp\u003eDelete all films but musicals:\u003c/p\u003e\u003cpre\u003eDELETE FROM films WHERE kind \u0026lt;\u0026gt; \u0026#39;Musical\u0026#39;;\n\u003c/pre\u003e\u003cp\u003eClear the table \u003ccode\u003efilms\u003c/code\u003e:\u003c/p\u003e\u003cpre\u003eDELETE FROM films;\n\u003c/pre\u003e\u003cp\u003eDelete completed tasks, returning full details of the deleted rows:\u003c/p\u003e\u003cpre\u003eDELETE FROM tasks WHERE status = \u0026#39;DONE\u0026#39; RETURNING *;\n\u003c/pre\u003e\u003cp\u003eDelete the row of \u003ccode\u003etasks\u003c/code\u003e on which the cursor \u003ccode\u003ec_tasks\u003c/code\u003e is currently positioned:\u003c/p\u003e\u003cpre\u003eDELETE FROM tasks WHERE CURRENT OF c_tasks;\n\u003c/pre\u003e\u003cp\u003eWhile there is no \u003ccode\u003eLIMIT\u003c/code\u003e clause for \u003ccode\u003eDELETE\u003c/code\u003e, it is possible to get a similar effect using the same method described in \u003ca href=\"/docs/18/sql-update.html#UPDATE-LIMIT\" rel=\"nofollow\"\u003ethe documentation of \u003ccode\u003eUPDATE\u003c/code\u003e\u003c/a\u003e:\u003c/p\u003e\u003cpre\u003eWITH delete_batch AS (\n  SELECT l.ctid FROM user_logs AS l\n    WHERE l.status = \u0026#39;archived\u0026#39;\n    ORDER BY l.creation_date\n    FOR UPDATE\n    LIMIT 10000\n)\nDELETE FROM user_logs AS dl\n  USING delete_batch AS del\n  WHERE dl.ctid = del.ctid;\n\u003c/pre\u003e\u003cp\u003eThis use of \u003ccode\u003ectid\u003c/code\u003e is only safe because the query is repeatedly run, avoiding the problem of changed \u003ccode\u003ectid\u003c/code\u003es.\u003c/p\u003e\u003c/section\u003e\u003csection\u003e\u003ch2\u003eCompatibility\u003c/h2\u003e\u003cp\u003eThis command conforms to the \u003cacronym\u003eSQL\u003c/acronym\u003e standard, except that the \u003ccode\u003eUSING\u003c/code\u003e and \u003ccode\u003eRETURNING\u003c/code\u003e clauses are \u003cspan\u003ePostgreSQL\u003c/span\u003e extensions, as is the ability to use \u003ccode\u003eWITH\u003c/code\u003e with \u003ccode\u003eDELETE\u003c/code\u003e.\u003c/p\u003e\u003c/section\u003e\u003csection\u003e\u003ch2\u003eSee Also\u003c/h2\u003e\u003cspan\u003e\u003ca href=\"/wiki/sql/truncate/?v=18\" title=\"TRUNCATE\" rel=\"nofollow\"\u003e\u003cspan\u003eTRUNCATE\u003c/span\u003e\u003c/a\u003e\u003c/span\u003e\u003c/section\u003e","SourceRevision":"wiki-repair-20261023:4de4e42ec2301dfa3e8b13684f2742a468c12b9beac552c8890d34850989f958","ContentHash":"25790ee8d6202669350c4d3550c2dc4014d2e52a6659c30a8434aeb120adfd44","Payload":{"related":["truncate"],"sections":[{"html":"\u003cp\u003e\u003ccode class=\"command\"\u003eDELETE\u003c/code\u003e deletes rows that satisfy the \u003ccode class=\"literal\"\u003eWHERE\u003c/code\u003e clause from the specified table. If the \u003ccode class=\"literal\"\u003eWHERE\u003c/code\u003e clause is absent, the effect is to delete all rows in the table. The result is a valid, but empty table.\u003c/p\u003e\u003cdiv class=\"tip\"\u003e\u003ch3\u003eTip\u003c/h3\u003e\u003cp\u003e\u003ca href=\"/docs/18/sql-truncate.html\" title=\"TRUNCATE\"\u003e\u003ccode class=\"command\"\u003eTRUNCATE\u003c/code\u003e\u003c/a\u003e provides a faster mechanism to remove all rows from a table.\u003c/p\u003e\u003c/div\u003e\u003cp\u003eThere are two ways to delete rows in a table using information contained in other tables in the database: using sub-selects, or specifying additional tables in the \u003ccode class=\"literal\"\u003eUSING\u003c/code\u003e clause. Which technique is more appropriate depends on the specific circumstances.\u003c/p\u003e\u003cp\u003eThe optional \u003ccode class=\"literal\"\u003eRETURNING\u003c/code\u003e clause causes \u003ccode class=\"command\"\u003eDELETE\u003c/code\u003e to compute and return value(s) based on each row actually deleted. Any expression using the table's columns, and/or columns of other tables mentioned in \u003ccode class=\"literal\"\u003eUSING\u003c/code\u003e, can be computed. The syntax of the \u003ccode class=\"literal\"\u003eRETURNING\u003c/code\u003e list is identical to that of the output list of \u003ccode class=\"command\"\u003eSELECT\u003c/code\u003e.\u003c/p\u003e\u003cp\u003eYou must have the \u003ccode class=\"literal\"\u003eDELETE\u003c/code\u003e privilege on the table to delete from it, as well as the \u003ccode class=\"literal\"\u003eSELECT\u003c/code\u003e privilege for any table in the \u003ccode class=\"literal\"\u003eUSING\u003c/code\u003e clause or whose values are read in the \u003cem class=\"replaceable\"\u003e\u003ccode\u003econdition\u003c/code\u003e\u003c/em\u003e.\u003c/p\u003e","key":"description","title":"Description"},{"html":"\u003cdiv class=\"variablelist\"\u003e\u003cdl class=\"variablelist\"\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003ewith_query\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eThe \u003ccode class=\"literal\"\u003eWITH\u003c/code\u003e clause allows you to specify one or more subqueries that can be referenced by name in the \u003ccode class=\"command\"\u003eDELETE\u003c/code\u003e query. See \u003ca href=\"/docs/18/queries-with.html\" title=\"7.8. WITH Queries (Common Table Expressions)\"\u003eSection 7.8\u003c/a\u003e and \u003ca href=\"/docs/18/sql-select.html\" title=\"SELECT\"\u003e\u003cspan class=\"refentrytitle\"\u003eSELECT\u003c/span\u003e\u003c/a\u003e for details.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003etable_name\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eThe name (optionally schema-qualified) of the table to delete rows from. If \u003ccode class=\"literal\"\u003eONLY\u003c/code\u003e is specified before the table name, matching rows are deleted from the named table only. If \u003ccode class=\"literal\"\u003eONLY\u003c/code\u003e is not specified, matching rows are also deleted from any tables inheriting from the named table. Optionally, \u003ccode class=\"literal\"\u003e*\u003c/code\u003e can be specified after the table name to explicitly indicate that descendant tables are included.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003ealias\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eA substitute name for the target table. When an alias is provided, it completely hides the actual name of the table. For example, given \u003ccode class=\"literal\"\u003eDELETE FROM foo AS f\u003c/code\u003e, the remainder of the \u003ccode class=\"command\"\u003eDELETE\u003c/code\u003e statement must refer to this table as \u003ccode class=\"literal\"\u003ef\u003c/code\u003e not \u003ccode class=\"literal\"\u003efoo\u003c/code\u003e.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003efrom_item\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eA table expression allowing columns from other tables to appear in the \u003ccode class=\"literal\"\u003eWHERE\u003c/code\u003e condition. This uses the same syntax as the \u003ca href=\"/docs/18/sql-select.html#SQL-FROM\" title=\"FROM Clause\"\u003e\u003ccode class=\"literal\"\u003eFROM\u003c/code\u003e\u003c/a\u003e clause of a \u003ccode class=\"command\"\u003eSELECT\u003c/code\u003e statement; for example, an alias for the table name can be specified. Do not repeat the target table as a \u003cem class=\"replaceable\"\u003e\u003ccode\u003efrom_item\u003c/code\u003e\u003c/em\u003e unless you wish to set up a self-join (in which case it must appear with an alias in the \u003cem class=\"replaceable\"\u003e\u003ccode\u003efrom_item\u003c/code\u003e\u003c/em\u003e).\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003econdition\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eAn expression that returns a value of type \u003ccode class=\"type\"\u003eboolean\u003c/code\u003e. Only rows for which this expression returns \u003ccode class=\"literal\"\u003etrue\u003c/code\u003e will be deleted.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003ecursor_name\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eThe name of the cursor to use in a \u003ccode class=\"literal\"\u003eWHERE CURRENT OF\u003c/code\u003e condition. The row to be deleted is the one most recently fetched from this cursor. The cursor must be a non-grouping query on the \u003ccode class=\"command\"\u003eDELETE\u003c/code\u003e's target table. Note that \u003ccode class=\"literal\"\u003eWHERE CURRENT OF\u003c/code\u003e cannot be specified together with a Boolean condition. See \u003ca href=\"/docs/18/sql-declare.html\" title=\"DECLARE\"\u003e\u003cspan class=\"refentrytitle\"\u003eDECLARE\u003c/span\u003e\u003c/a\u003e for more information about using cursors with \u003ccode class=\"literal\"\u003eWHERE CURRENT OF\u003c/code\u003e.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003eoutput_alias\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eAn optional substitute name for \u003ccode class=\"literal\"\u003eOLD\u003c/code\u003e or \u003ccode class=\"literal\"\u003eNEW\u003c/code\u003e rows in the \u003ccode class=\"literal\"\u003eRETURNING\u003c/code\u003e list.\u003c/p\u003e\u003cp\u003eBy default, old values from the target table can be returned by writing \u003ccode class=\"literal\"\u003eOLD.\u003cem class=\"replaceable\"\u003e\u003ccode\u003ecolumn_name\u003c/code\u003e\u003c/em\u003e\u003c/code\u003e or \u003ccode class=\"literal\"\u003eOLD.*\u003c/code\u003e, and new values can be returned by writing \u003ccode class=\"literal\"\u003eNEW.\u003cem class=\"replaceable\"\u003e\u003ccode\u003ecolumn_name\u003c/code\u003e\u003c/em\u003e\u003c/code\u003e or \u003ccode class=\"literal\"\u003eNEW.*\u003c/code\u003e. When an alias is provided, these names are hidden and the old or new rows must be referred to using the alias. For example \u003ccode class=\"literal\"\u003eRETURNING WITH (OLD AS o, NEW AS n) o.*, n.*\u003c/code\u003e.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003eoutput_expression\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eAn expression to be computed and returned by the \u003ccode class=\"command\"\u003eDELETE\u003c/code\u003e command after each row is deleted. The expression can use any column names of the table named by \u003cem class=\"replaceable\"\u003e\u003ccode\u003etable_name\u003c/code\u003e\u003c/em\u003e or table(s) listed in \u003ccode class=\"literal\"\u003eUSING\u003c/code\u003e. Write \u003ccode class=\"literal\"\u003e*\u003c/code\u003e to return all columns.\u003c/p\u003e\u003cp\u003eA column name or \u003ccode class=\"literal\"\u003e*\u003c/code\u003e may be qualified using \u003ccode class=\"literal\"\u003eOLD\u003c/code\u003e or \u003ccode class=\"literal\"\u003eNEW\u003c/code\u003e, or the corresponding \u003cem class=\"replaceable\"\u003e\u003ccode\u003eoutput_alias\u003c/code\u003e\u003c/em\u003e for \u003ccode class=\"literal\"\u003eOLD\u003c/code\u003e or \u003ccode class=\"literal\"\u003eNEW\u003c/code\u003e, to cause old or new values to be returned. An unqualified column name, or \u003ccode class=\"literal\"\u003e*\u003c/code\u003e, or a column name or \u003ccode class=\"literal\"\u003e*\u003c/code\u003e qualified using the target table name or alias will return old values.\u003c/p\u003e\u003cp\u003eFor a simple \u003ccode class=\"command\"\u003eDELETE\u003c/code\u003e, all new values will be \u003ccode class=\"literal\"\u003eNULL\u003c/code\u003e. However, if an \u003ccode class=\"literal\"\u003eON DELETE\u003c/code\u003e rule causes an \u003ccode class=\"command\"\u003eINSERT\u003c/code\u003e or \u003ccode class=\"command\"\u003eUPDATE\u003c/code\u003e to be executed instead, the new values may be non-\u003ccode class=\"literal\"\u003eNULL\u003c/code\u003e.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003eoutput_name\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eA name to use for a returned column.\u003c/p\u003e\u003c/dd\u003e\u003c/dl\u003e\u003c/div\u003e","key":"parameters","title":"Parameters"},{"html":"\u003cp\u003eOn successful completion, a \u003ccode class=\"command\"\u003eDELETE\u003c/code\u003e command returns a command tag of the form\u003c/p\u003e\u003cpre class=\"screen\"\u003eDELETE \u003cem class=\"replaceable\"\u003e\u003ccode\u003ecount\u003c/code\u003e\u003c/em\u003e\n\u003c/pre\u003e\u003cp\u003eThe \u003cem class=\"replaceable\"\u003e\u003ccode\u003ecount\u003c/code\u003e\u003c/em\u003e is the number of rows deleted. Note that the number may be less than the number of rows that matched the \u003cem class=\"replaceable\"\u003e\u003ccode\u003econdition\u003c/code\u003e\u003c/em\u003e when deletes were suppressed by a \u003ccode class=\"literal\"\u003eBEFORE DELETE\u003c/code\u003e trigger. If \u003cem class=\"replaceable\"\u003e\u003ccode\u003ecount\u003c/code\u003e\u003c/em\u003e is 0, no rows were deleted by the query (this is not considered an error).\u003c/p\u003e\u003cp\u003eIf the \u003ccode class=\"command\"\u003eDELETE\u003c/code\u003e command contains a \u003ccode class=\"literal\"\u003eRETURNING\u003c/code\u003e clause, the result will be similar to that of a \u003ccode class=\"command\"\u003eSELECT\u003c/code\u003e statement containing the columns and values defined in the \u003ccode class=\"literal\"\u003eRETURNING\u003c/code\u003e list, computed over the row(s) deleted by the command.\u003c/p\u003e","key":"outputs","title":"Outputs"},{"html":"\u003cp\u003e\u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e lets you reference columns of other tables in the \u003ccode class=\"literal\"\u003eWHERE\u003c/code\u003e condition by specifying the other tables in the \u003ccode class=\"literal\"\u003eUSING\u003c/code\u003e clause. For example, to delete all films produced by a given producer, one can do:\u003c/p\u003e\u003cpre class=\"programlisting\"\u003eDELETE FROM films USING producers\n  WHERE producer_id = producers.id AND producers.name = 'foo';\n\u003c/pre\u003e\u003cp\u003eWhat is essentially happening here is a join between \u003ccode class=\"structname\"\u003efilms\u003c/code\u003e and \u003ccode class=\"structname\"\u003eproducers\u003c/code\u003e, with all successfully joined \u003ccode class=\"structname\"\u003efilms\u003c/code\u003e rows being marked for deletion. This syntax is not standard. A more standard way to do it is:\u003c/p\u003e\u003cpre class=\"programlisting\"\u003eDELETE FROM films\n  WHERE producer_id IN (SELECT id FROM producers WHERE name = 'foo');\n\u003c/pre\u003e\u003cp\u003eIn some cases the join style is easier to write or faster to execute than the sub-select style.\u003c/p\u003e","key":"notes","title":"Notes"},{"html":"\u003cp\u003eDelete all films but musicals:\u003c/p\u003e\u003cpre class=\"programlisting\"\u003eDELETE FROM films WHERE kind \u0026lt;\u0026gt; 'Musical';\n\u003c/pre\u003e\u003cp\u003eClear the table \u003ccode class=\"literal\"\u003efilms\u003c/code\u003e:\u003c/p\u003e\u003cpre class=\"programlisting\"\u003eDELETE FROM films;\n\u003c/pre\u003e\u003cp\u003eDelete completed tasks, returning full details of the deleted rows:\u003c/p\u003e\u003cpre class=\"programlisting\"\u003eDELETE FROM tasks WHERE status = 'DONE' RETURNING *;\n\u003c/pre\u003e\u003cp\u003eDelete the row of \u003ccode class=\"structname\"\u003etasks\u003c/code\u003e on which the cursor \u003ccode class=\"literal\"\u003ec_tasks\u003c/code\u003e is currently positioned:\u003c/p\u003e\u003cpre class=\"programlisting\"\u003eDELETE FROM tasks WHERE CURRENT OF c_tasks;\n\u003c/pre\u003e\u003cp\u003eWhile there is no \u003ccode class=\"literal\"\u003eLIMIT\u003c/code\u003e clause for \u003ccode class=\"command\"\u003eDELETE\u003c/code\u003e, it is possible to get a similar effect using the same method described in \u003ca href=\"/docs/18/sql-update.html#UPDATE-LIMIT\"\u003ethe documentation of \u003ccode class=\"command\"\u003eUPDATE\u003c/code\u003e\u003c/a\u003e:\u003c/p\u003e\u003cpre class=\"programlisting\"\u003eWITH delete_batch AS (\n  SELECT l.ctid FROM user_logs AS l\n    WHERE l.status = 'archived'\n    ORDER BY l.creation_date\n    FOR UPDATE\n    LIMIT 10000\n)\nDELETE FROM user_logs AS dl\n  USING delete_batch AS del\n  WHERE dl.ctid = del.ctid;\n\u003c/pre\u003e\u003cp\u003eThis use of \u003ccode class=\"structfield\"\u003ectid\u003c/code\u003e is only safe because the query is repeatedly run, avoiding the problem of changed \u003ccode class=\"structfield\"\u003ectid\u003c/code\u003es.\u003c/p\u003e","key":"examples","title":"Examples"},{"html":"\u003cp\u003eThis command conforms to the \u003cacronym\u003eSQL\u003c/acronym\u003e standard, except that the \u003ccode class=\"literal\"\u003eUSING\u003c/code\u003e and \u003ccode class=\"literal\"\u003eRETURNING\u003c/code\u003e clauses are \u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e extensions, as is the ability to use \u003ccode class=\"literal\"\u003eWITH\u003c/code\u003e with \u003ccode class=\"command\"\u003eDELETE\u003c/code\u003e.\u003c/p\u003e","key":"compatibility","title":"Compatibility"},{"html":"\u003cspan class=\"simplelist\"\u003e\u003ca href=\"/wiki/sql/truncate/?v=18\" title=\"TRUNCATE\"\u003e\u003cspan class=\"refentrytitle\"\u003eTRUNCATE\u003c/span\u003e\u003c/a\u003e\u003c/span\u003e","key":"see_also","title":"See Also"}],"sections_same_as":"","synopsis_html":"[ WITH [ RECURSIVE ] \u003cem class=\"replaceable\"\u003e\u003ccode\u003ewith_query\u003c/code\u003e\u003c/em\u003e [, ...] ]\nDELETE FROM [ ONLY ] \u003cem class=\"replaceable\"\u003e\u003ccode\u003etable_name\u003c/code\u003e\u003c/em\u003e [ * ] [ [ AS ] \u003cem class=\"replaceable\"\u003e\u003ccode\u003ealias\u003c/code\u003e\u003c/em\u003e ]\n    [ USING \u003cem class=\"replaceable\"\u003e\u003ccode\u003efrom_item\u003c/code\u003e\u003c/em\u003e [, ...] ]\n    [ WHERE \u003cem class=\"replaceable\"\u003e\u003ccode\u003econdition\u003c/code\u003e\u003c/em\u003e | WHERE CURRENT OF \u003cem class=\"replaceable\"\u003e\u003ccode\u003ecursor_name\u003c/code\u003e\u003c/em\u003e ]\n    [ RETURNING [ WITH ( { OLD | NEW } AS \u003cem class=\"replaceable\"\u003e\u003ccode\u003eoutput_alias\u003c/code\u003e\u003c/em\u003e [, ...] ) ]\n                { * | \u003cem class=\"replaceable\"\u003e\u003ccode\u003eoutput_expression\u003c/code\u003e\u003c/em\u003e [ [ AS ] \u003cem class=\"replaceable\"\u003e\u003ccode\u003eoutput_name\u003c/code\u003e\u003c/em\u003e ] } [, ...] ]","synopsis_text":"[ WITH [ RECURSIVE ] with_query [, ...] ]\nDELETE FROM [ ONLY ] table_name [ * ] [ [ AS ] alias ]\n[ USING from_item [, ...] ]\n[ WHERE condition | WHERE CURRENT OF cursor_name ]\n[ RETURNING [ WITH ( { OLD | NEW } AS output_alias [, ...] ) ]\n{ * | output_expression [ [ AS ] output_name ] } [, ...] ]"}},"RequestedLocale":"en","Fallback":false,"Versions":["10","11","12","13","14","15","16","17","18","19","20","6.4","6.5","7.0","7.1","7.2","7.3","7.4","8.0","8.1","8.2","8.3","8.4","9.0","9.1","9.2","9.3","9.4","9.5","9.6"],"Locales":["en","zh-Hans"],"Signatures":null,"Spellings":null,"SQLState":null,"Evidence":null}