Update using Common Table Expression-CodePudding

I have a pair of tables in a one to many relationship. I want to insert rows in the One table, rows in the Many table, and then update the new row in the One table.

A simplified structure is as below. There is a fiddle at https://dbfiddle.uk/?rdbms=postgres_12&fiddle=8555fb74372b91c8854f7a18e78110c0

Everything works except for the last step, where the new rows is supposed to be updated. I get a NULL for the updated value.

I can’t see what is missing to get this to work. I suspect it has something to do with the correct syntax to update using the FROM clause. According to the documentation, that’s how you can supply additional data to the UPDATE statement. On the other hand, there may be something more fundamental.

I know that I can write a function to do this, and I have already done that. Here I want to get a better understanding of the SQL involved.

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;

In the above sample, the first two CTEs, customer and items are simply there to provide values for what follows. The newsale CTE creates the new row in the One table, addsaleitems adds rows to the Many table, and the rest of it is supposed to update the One table.

CodePudding user response：

As documented, the UPDATE cannot yet see the rows inserted in the same statement.

The trick is to calculate the sum first, right when you insert the row into testsales.