Returning complex query on update SQL

Question

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_query

Desired result on update:

id	name	date	orders	store_id	store_name
1	ABC	11/16/2022	10	1	jj

@JonasMetzler im sorry for confusing question. I want to return the first query on returning in update sql. I don't want to call 2 separate queries — Mj Ebrahimzadeh
– Mj Ebrahimzadeh, Commented Nov 16, 2022 at 10:43

O. Jones · Accepted Answer · 2022-11-16 10:46:34Z

2

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.

answered Nov 16, 2022 at 10:46

O. Jones

109k17 gold badges134 silver badges187 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Mj Ebrahimzadeh Over a year ago

Thank you that was my exact question You want to do both the update and a SELECT operation in one go. But that's not possible.

O. Jones Over a year ago

You're welcome. SQL takes some getting used to, eh?

Collectives™ on Stack Overflow

Returning complex query on update SQL

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related