Oracle SQL更新语句以哪种顺序更改列值?

时间:2022-12-10 07:53:22

I got this statement

我收到了这个声明

UPDATE TABLE_A SET COL_A = COL_B, COL_B = 0

I am curious about the sequence it execute because I expect COL_A should contain the value of my COL_B and COL_B should be 0 BUT somehow currently both fields are 0.

我很好奇它执行的序列,因为我希望COL_A应该包含我的COL_B的值,COL_B应该是0但是当然两个字段都是0。

2 个解决方案

#1


1  

CREATE TABLE test2(A NUMBER, B NUMBER);

INSERT INTO TEST2 VALUES(1,2);

UPDATE TEST2 SET A=B,B=0;

A=2,B=0 after execute

执行后A = 2,B = 0

#2


2  

SQL updates are atomic in nature - there is no concept of "sequence" or "order" in which individual columns are updated. You can put them in any order you like, it doesn't matter.

SQL更新本质上是原子的 - 没有“序列”或“顺序”的概念,其中单个列被更新。你可以按照自己喜欢的顺序放置它们,这没关系。

Conceptually, you can think of it taking the "before" state of the row and changing it into the "after" state of the row. So COL_A will be updated with whatever value was in COL_B prior to the update.

从概念上讲,您可以将其视为行的“之前”状态并将其更改为行的“之后”状态。因此,COL_A将在更新之前使用COL_B中的任何值进行更新。

This makes it easy to swap two values:

这样可以轻松交换两个值:

UPDATE test2 SET A=B, B=A;

#1


1  

CREATE TABLE test2(A NUMBER, B NUMBER);

INSERT INTO TEST2 VALUES(1,2);

UPDATE TEST2 SET A=B,B=0;

A=2,B=0 after execute

执行后A = 2,B = 0

#2


2  

SQL updates are atomic in nature - there is no concept of "sequence" or "order" in which individual columns are updated. You can put them in any order you like, it doesn't matter.

SQL更新本质上是原子的 - 没有“序列”或“顺序”的概念,其中单个列被更新。你可以按照自己喜欢的顺序放置它们,这没关系。

Conceptually, you can think of it taking the "before" state of the row and changing it into the "after" state of the row. So COL_A will be updated with whatever value was in COL_B prior to the update.

从概念上讲,您可以将其视为行的“之前”状态并将其更改为行的“之后”状态。因此,COL_A将在更新之前使用COL_B中的任何值进行更新。

This makes it easy to swap two values:

这样可以轻松交换两个值:

UPDATE test2 SET A=B, B=A;