使用公共表表达式更新

Question

我有一对处于一对多关系的表。我想在一个表中插入行，在多个表中插入行，然后在一个表中更新新行。

简化的结构如下。有一个小提琴 https://dbfiddle.uk/?rdbms=postgres_12&fiddle=8555fb74372b91c8854f7a18e78110c0

除了最后一步（应该更新新行）之外，一切正常。我得到更新值的NULL。

我看不出要让它发挥作用还缺少什么。我怀疑这与使用 FROM 子句更新的正确语法有关。根据文档，这就是如何向 UPDATE 语句提供附加数据的方法。另一方面，可能还有更根本的东西。

我知道我可以编写一个函数来做到这一点，而且我已经做到了。这里我想更好地理解涉及到的SQL。

CREATE TABLE testsales(
    id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    customerid int,
    total decimal(6,2)
);
CREATE TABLE testitems(
    id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    saleid int REFERENCES testsales(id),
    productid int,
    quantity int,
    price decimal(5,2)
);

with
    customer(id) as (select 562),
    items(productid,quantity) as (values(1489,2),(746,1),(1093,3)),
    newsale as (
        insert into testsales(customerid)
        select id FROM customer
        returning id
    ),
    addsaleitems as(
        insert into testitems(saleid,productid,quantity,price)
        select
            newsale.id, items.productid, items.quantity,100
        from newsale, items
        returning *
    )
update testsales
set total=(select sum(price*quantity) from addsaleitems)
from addsaleitems,newsale
where testsales.id=newsale.id;

在上面的示例中，前两个 CTE，customer 和 items 只是为后面的内容提供值。 newsale CTE 在 One 表中创建新行，addsaleitems 将行添加到 Many 表中，其余部分应该更新一张桌子。

Answer 1

据记录，更新 尚无法看到同一语句中插入的行。

诀窍是在将行插入 testsales 时首先计算总和。