CODEWITHSUNDEEP
sqlpostgresqlcommon-table-expression
SMW
SMW

Reputation: 339

PostgreSQL WITH query

This is what is written in the manual:

WITH t AS (
    UPDATE products SET price = price * 1.05
    RETURNING *
)
SELECT * FROM products;

the outer SELECT would return the original prices before the action of the UPDATE, while in

WITH t AS (
    UPDATE products SET price = price * 1.05
    RETURNING *
)
SELECT * FROM t;

the outer SELECT would return the updated data.

My question is.... What if I want to have a column that shows the diffrance between the before and after update data? for example if it was 5 then changed to 6. I want to see 1. in order to do that I need price column before and after. Something like:

WITH t AS (
    UPDATE products SET price = price * 1.05
    RETURNING *
)
SELECT *,new.price-old.price FROM ?;

None of the examples above shows how to do it. I know this can be solved with other ways but I want to know if it's possible to do it with WITH statment.

Upvotes: 0

Views: 56

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269753

You can join them together:

WITH t AS (
    UPDATE products SET price = price * 1.05
    RETURNING *
)
SELECT p.*, t.price, (t.price - old.price) as diff
FROM products p JOIN
     t
     ON p.productid = t.productid;

Upvotes: 1

Related Questions