SQL UPDATE 在设置之前读取列值 [英] SQL UPDATE read column values before setting

问题描述

我在 SO 和 google 上都搜索过这个信息，但没有找到任何权威的答案.

I have searched for this information both on SO and on google, but have not found any authoritative answer.

当您有如下更新语句时:

When you have an update statement like:

UPDATE table SET rowA = rowB, rowB = NULL ...

看来:

• 排序并不重要(UPDATE table SET rowB = NULL, rowA = rowB)
• 尽管如此，结果是 rowA 获取了 rowB 中的 prev 值，因为 UPDATE 似乎首先读取之前的值，然后更新它们.

我想知道以上两点对于 SQL 来说是否一般正确，即它们是否是SQL UPDATE 语义的一部分，如果它们在标准，或者如果它是一个实现细节(因此可能会发生变化).谢谢！

I would like to know if the two above points are true in general for SQL, i.e. if they are part of the SQL UPDATE semantics, if they are in the standard, or if it is an implementation details (and therefore subject to change). Thanks!

让我强调一下，我想要一个权威"的答案；我已经对一些 SQL 实现进行了测试，这些行为确实是这里描述的行为.我需要的是一个证明"，证明这实际上是在 SQL 标准/规范/UPDATE 的语义中，并带有指向该标准的链接，或者指向一个代理可靠源(MSDN、dev.mysql.com、Oracle 或 PostgreSQL文档，...)

Let me stress that I would like an "authoritative" answer; I already tested on a number of SQL implementation that the behaviour is indeed the one depicted here. What I need is a "proof" that this is actually in the SQL standard/spec/semantics of UPDATE, with a link to the standard or, alternatively, to a surrogate reliable source (MSDN, dev.mysql.com, Oracle or PostgreSQL docs, ...)

推荐答案

James R. Groff, Paul N. Weinberg:SQL The complete reference (Osborne 1999)，第 209 页陈述

开始报价

如果赋值列表中的表达式引用了目标表的列之一，用于计算表达式的值是当前行中该列的值在应用任何更新之前.出现在WHERE 子句.例如，考虑这个(有点做作的)UPDATE 语句:

If an expression in the assignment list references one of the columns of the target table, the value used to calculate the expression is the value of that column in the current row before any updates are applied. The same is true of column references that occur in the WHERE clause. For example, consider this (somewhat contrived) UPDATE statement:

UPDATE OFFICES
 SET QUOTA = 400000.00, SALES = QUOTA
WHERE QUOTA < 400000.00

更新前，Bill Adams 的 QUOTA 值为 350,000 美元，SALES 值为367,911 美元.更新后，他的行的 SALES 值为 350,000 美元，而不是 400,000 美元.这因此 SET 子句中的赋值顺序无关紧要；作业可以是以任意顺序指定.

Before the update, Bill Adams had a QUOTA value of $350,000 and a SALES value of $367,911. After the update, his row has a SALES value of $350,000, not $400,000. The order of the assignments in the SET clause is thus immaterial; the assignments can be specified in any order.

结束引用

ANSI-92 SQL 标准 (X3H2-93-004) 草案的第 13.9 章第 6 项第 393 页也支持这一点，发现 这里.

The same is supported by chapter 13.9 item 6, page 393, of a draft to the ANSI-92 SQL standard (X3H2-93-004), found here.

这是迄今为止我能得到的最独立和最接近的实现.

This is the most implementaion independent and the closest I could get so far.

可以找到 X3H2-93-004 的其他来源，例如这里 (pg590，第 15 条)

Other sources of X3H2-93-004 can be found e.g. here (pg 590, item 15)

这篇关于SQL UPDATE 在设置之前读取列值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！