Postgres: UPDATE and return result of SELECT statement

Question

My table may look like this:

created	last_read	data
2021-10-15 15:51:50	NULL	BINARY
2021-10-14 12:22:13	NULL	BINARY
2021-10-13 06:32:44	2021-10-14 16:44:08	BINARY
2021-10-12 16:05:07	2021-10-13 17:21:09	BINARY

What I want to do: SELECT x rows ordered by created ASC and last_read ASC and set time last_read of the selected rows to NOW().

The SELECT would look like this:

SELECT data
FROM my_table
ORDER BY last_read NULLS FIRST, created
LIMIT 3

I would then take the result set and apply the following UPDATE statement:

UPDATE my_table
SET last_read = NOW()

Question: How would a query look like that does both of the above and returns the result of the SELECT?

If the returned data is before or prior to the UPDATE doesn’t matter, since I only select the data column.

I’ve tried

UPDATE my_table
SET last_read = NOW()
RETURNING data

However, I cannot provide ORDER BY and LIMIT to this. Therefore, it doesn't produce the desired result, but updates and returns all rows.

I may also think of using a subquery to update rows based on the SELECT. But then those rows wouldn’t be returned as a result.

How do know in advance that LIMIT 3 will get all the rows where last_read is null' , And why would you want to update the date on the third row? Yes it was selected but only as side effect of the limit and number of null values. — Belayer
– Belayer, Commented Oct 21, 2021 at 17:43
I don’t want to just fetch the rows where last_read is null, but all of them (up to limit). last_read = null should only be prioritized when ordering. — Rob
– Rob, Commented Oct 22, 2021 at 10:03

id'7238 · Accepted Answer · 2021-10-21 16:45:11Z

2

WITH cte AS (
  SELECT *
  FROM mytable
  ORDER BY last_read NULLS FIRST, created
  LIMIT 3
)
UPDATE mytable t
SET last_read = NOW()
FROM cte
WHERE cte.created = t.created
RETURNING t.data;

db<>fiddle

answered Oct 21, 2021 at 16:45

id'7238

2,7261 gold badge6 silver badges12 bronze badges

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

Collectives™ on Stack Overflow

Postgres: UPDATE and return result of SELECT statement

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related