英文:
How to get value before update
问题
表格已使用其他表格中的值进行更新。
如何获取更新前后的值？
尝试使用以下代码：
CREATE TEMP TABLE change (id INTEGER, testvalue VARCHAR);
INSERT INTO change VALUES (1, 'old');
CREATE TEMP TABLE newvalue (id INTEGER, testvalue VARCHAR);
INSERT INTO newvalue VALUES (1, 'new');
UPDATE change
SET testvalue = newvalue.testvalue
FROM newvalue
WHERE change.id = newvalue.id
RETURNING change.testvalue, newvalue.testvalue;
但是返回的结果是：
> new, new
如何获得以下结果？
> old, new
英文:
Table is updated with value from other table.
How to get values before and after update ?
Tried
CREATE temp table change ( id integer, testvalue varchar );
insert into change values (1, 'old' );
CREATE temp table newvalue ( id integer, testvalue varchar );
insert into newvalue values (1, 'new' );
update change set testvalue =newvalue.testvalue
from newvalue
where change.id=newvalue.id
returning change.testvalue, newvalue.testvalue
but in returns
> new, new
How to get
> old, new
result?
答案1
得分: 2
>可选的RETURNING子句会导致UPDATE计算并返回基于每个实际更新的行的值。可以计算使用表的列和/或在FROM中提到的其他表的列的任何表达式。使用表的列的新（更新后）值。RETURNING列表的语法与SELECT的输出列表完全相同。
因此，要获取“old2”值，表必须在“FROM”子句中，如下所示：
CREATE TEMP TABLE change (id INTEGER, testvalue VARCHAR);
INSERT INTO change VALUES (1, 'old');
CREATE TEMP TABLE newvalue (id INTEGER, testvalue VARCHAR);
INSERT INTO newvalue VALUES (1, 'new');
UPDATE change SET testvalue = newvalue.testvalue
FROM change c1 JOIN newvalue ON c1.id = newvalue.id
WHERE change.id = newvalue.id
RETURNING c1.testvalue, newvalue.testvalue
> status > CREATE TABLE >
> status > INSERT 0 1 >
> status > CREATE TABLE >
> status > INSERT 0 1 >
| testvalue | testvalue |
|---|---|
| old | new |
| > ``` status | |
| > UPDATE 1 | |
| > ``` |
英文:
The manual explains:
>The optional RETURNING clause causes UPDATE to compute and return value(s) based on each row actually updated. Any expression using the table's columns, and/or columns of other tables mentioned in FROM, can be computed. The new (post-update) values of the table's columns are used. The syntax of the RETURNING list is identical to that of the output list of SELECT.
so to get the "old2 value the table must be in the FROM clause like
CREATE temp table change ( id integer, testvalue varchar );
insert into change values (1, 'old' );
CREATE temp table newvalue ( id integer, testvalue varchar );
insert into newvalue values (1, 'new' );
update change set testvalue =newvalue.testvalue
from change c1 JOIN newvalue ON c1.id=newvalue.id
where change.id=newvalue.id
returning c1.testvalue, newvalue.testvalue
> status
> CREATE TABLE
>
> status
> INSERT 0 1
>
> status
> CREATE TABLE
>
> status
> INSERT 0 1
>
| testvalue | testvalue |
|---|---|
| old | new |
| > ``` status | |
| > UPDATE 1 | |
| > ``` |
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论