根据另一个表中的值更新2列

I have 2 tables and I want to write 2 updates.
tbl_stage with id, flag (null) and count(null) column.
tbl_source with id, count, updated column.

我有2个表，我想写2个更新。带有id，flag（null）和count（null）列的tbl_stage。 tbl_source，带有id，count，更新列。

Before update

更新前

tbl_stage                 tbl_source
id    flag    count       id    count    updated
abc   NULL    NULL        abc   9.0      false
                          def   3.6      false

I want to update tbl_stage.count=tbl_source.count and tbl_stage.flag = true where tbl_stage.id=tbl_source.id.
And another, tbl_source.updated = true where tbl_stage.id=tbl_source.id.

我想更新tbl_stage.count = tbl_source.count和tbl_stage.flag = true，其中tbl_stage.id = tbl_source.id。另外，tbl_source.updated = true其中tbl_stage.id = tbl_source.id。

After update should look like this.

更新后应该是这样的。

tbl_stage                 tbl_source
id    flag    count       id    count    updated
abc   true    9.0         abc   9.0      true
                          def   3.6      false

2 个解决方案

#1

You need to use 2 UPDATE statements for sure as updating 2 tables in one statement is not available.

您需要使用2个UPDATE语句，因为在一个语句中更新2个表不可用。

update tbl_stage
set [count]=tbl_source.count 
    ,flag = 1
FROM tbl_source WHERE tbl_stage.id=tbl_source.id

update tbl_source
set updated = 1
from tbl_source WHERE tbl_stage.id=tbl_source.id
and tbl_stage.count=tbl_source.count

#2

You can do this with a single statement, changing both tables in one go:

您可以使用单个语句执行此操作，一次更改两个表：

with stage_update as (
  update stage
     set cnt = s.cnt, 
        flag = true
  from source s
  where stage.id = s.id
  returning s.id
)
update source
  set updated = true
where id in (select id from stage_update);

^{(I chose cnt for the column name because count is a reserved word, and should not be used as an identifier)}

（我为列名选择了cnt，因为count是保留字，不应该用作标识符）

#1