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.
CodePudding user response：
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;