什么是“E153 带子查询的可更新查询”在 SQL 标准中？

发布于 2024-08-17 18:56:34 字数 395 浏览 5 评论 0 原文

我手头没有（昂贵的）SQL 标准； SQL core/foundation 中的可更新查询是什么？

我发现 PostgreSQL 不支持它们，但其他一些数据库支持；您能给我指出有关它们在这些数据库中如何工作的文档吗？

PostgreSQL 通过规则实现查询重写和可更新视图系统；这有什么不同吗？

原文

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

━╋う一瞬間旳綻放 2024-08-24 18:56:34

我刚刚在 Wikipedia 上找到了这个 Oracle 示例，它看起来像更新左侧的 SELECT：

UPDATE (
SELECT *
  FROM articles  
  JOIN classification c 
    ON a.articleID = c.articleID 
) AS a
SET a.[updated_column] = updatevalue
WHERE c.classID = 1

而大多数数据库需要将查询编写为：

UPDATE a
SET a.[updated_column] = updatevalue
FROM articles a 
JOIN classification c 
ON a.articleID = c.articleID 
WHERE c.classID = 1

链接：oracle 参考文档，示例。

其他数据库支持这个吗？它记录在哪里？

现在我已经把我肮脏的手套戴在了标准上，我就引用它吧。

E153指的是：

第 7.12 款，“<查询
表达式>”：<查询表达式>是
可更新，即使其包含一个<子查询>

由于查询表达式也是一个表（不直观，但在 SQL99 4.16.3 涉及表的操作中），这意味着查询表达式是一个“可更新的表”。根据 4.16，这意味着我可以插入和删除它们。

这意味着我可以运行上面的代码，以及：

DELETE FROM (SELECT * FROM t1 JOIN t2 WHERE t1c1 = t2c3);

还有一些更多的规则来确定哪些查询表达式是可更新的，包含在 sql99-foundation 7.11 和 7.12 中；他们相当参与。 PostgreSQL 不允许更新非表名的查询表达式。可更新视图方面正在开展一些工作。我不确定该功能在视图之外有多有用，但该标准绝对很有趣，而且比预期的更奇怪。

【这里感觉很孤独。选择我的答案为最佳答案。]

I just found this Oracle example on Wikipedia that looks like a SELECT in the left-hand side of an update:

UPDATE (
SELECT *
  FROM articles  
  JOIN classification c 
    ON a.articleID = c.articleID 
) AS a
SET a.[updated_column] = updatevalue
WHERE c.classID = 1

Whereas most databases need the query to be written as:

UPDATE a
SET a.[updated_column] = updatevalue
FROM articles a 
JOIN classification c 
ON a.articleID = c.articleID 
WHERE c.classID = 1

Links: The oracle reference docs, examples.

Do other databases support this? Where is it documented?

Having now laid my grubby mittens on the standard, I'll just quote it.

E153 refers to:

Subclause 7.12, “<query
expression>”: A <query expression> is
updatable even though its <where
clause> contains a <subquery>

Since a query expressions is also a table (not intuitive but it's in SQL99 4.16.3 Operations involving tables), it means the query expression is an “udpatable table”. Which according to 4.16 means I can INSERT into and DELETE from them.

Which means I can run the above, as well as:

DELETE FROM (SELECT * FROM t1 JOIN t2 WHERE t1c1 = t2c3);

There are some more rules to determine what query expressions are updatable, contained in sql99-foundation 7.11 and 7.12; they are rather involved. PostgreSQL doesn't let query expressions that aren't table names be updatable. There is some work being done on updatable views. I'm not sure how useful the feature is outside of views, but the standard is definitely interesting, and weirder than expected.

[It's feeling lonely here. Picking my answer as best.]

回复收藏 0 原文

~没有更多了~