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 实现进行了测试，该行为确实是此处描述的行为.我需要的是一个证明"，证明这实际上在 UPDATE 的 SQL 标准/规范/语义中，带有标准的链接，或者替代可靠来源(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 完整参考(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) 草案的第 393 页第 13.9 章第 6 项支持相同，发现 这里.

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 的其他来源，例如这里 (pg第 590 章，第 15 条)

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

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