I want to return query with multiple joins and with clause after updating something.
For example my query is:
WITH orders AS (
SELECT product_id, SUM(amount) AS orders
FROM orders_summary
GROUP BY product_id
)
SELECT p.id, p.name,
p.date_of_creation,
o.orders, s.id AS store_id,
s.name AS store_name
FROM products AS p
LEFT JOIN orders AS o
ON p.id = o.product_id
LEFT JOIN stores AS s
ON s.id = p.store_id
WHERE p.id = '1'
| id | name | date | orders | store_id | store_name |
|---|---|---|---|---|---|
| 1 | pen | 11/16/2022 | 10 | 1 | jj |
| 2 | pencil | 11/10/2022 | 30 | 2 | ff |
I want to return the exact query but with updated result in my update:
UPDATE products
SET name = 'ABC'
WHERE id = '1'
RETURNING up_qeury
Desired result on update:
| id | name | date | orders | store_id | store_name |
|---|---|---|---|---|---|
| 1 | ABC | 11/16/2022 | 10 | 1 | jj |
>Solution :
You can try UPDATE products ... RETURNING *. That may get you the content of the row you just updated.
As for UPDATE .... RETURNING someQuery, You Can’t Do That™. You want to do both the update and a SELECT operation in one go. But that’s not possible.
If you must be sure your SELECT works on the precisely the same data as you just UPDATEd, you can wrap your two queries in a BEGIN; / COMMIT; transaction. That prevents concurrent users from making changes between your UPDATE and SELECT.