Postgres 16

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table thing (
  id bigserial,
  name text,
  thing_id text, 
  c_id text,
  status text, 
  etag text, 
  last_modified text,
  size int,
  status_1_date timestamp
);

CREATE TABLE


INSERT INTO thing (name,thing_id, c_id,status, etag, last_modified, size, status_1_date)
values 
('protocol://full/path/to/thing/thing_1.file1', 'thing_1','c_id', 'status_1', 'etag', '1111', 200, '2023-09-29 09:00:01'),
('somethin gelse', 'thing_1','c_id', 'status_1', 'etag', '1111', 200, '2023-09-29 09:00:01'),
('protocol://full/path/to/thing/thing_1.file2', 'thing_1','c_id', 'status_1', 'etag', '1111', 200, '2023-09-29 09:00:02'),
('protocol://full/path/to/thing/thing_2.file1', 'thing_2','c_id', 'status_1', 'etag', '1111', 200, '2023-09-29 09:00:03'),
('protocol://full/path/to/thing/thing_2.file2', 'thing_2','c_id', 'status_1', 'etag', '1111', 200, '2023-09-29 09:00:04'),
('protocol://full/path/to/thing/thing_2.file3', 'thing_2','c_id', 'status_1', 'etag', '1111', 200, '2023-09-29 09:00:05'),
('protocol://full/path/to/thing/thing_3.file1', 'thing_3','c_id', 'status_1', 'etag', '1111', 200, '2023-09-29 09:00:06');

INSERT 0 7


select * from thing;


id
name
thing_id
c_id
status
etag
last_modified
size
status_1_date


1
protocol://full/path/to/thing/thing_1.file1
thing_1
c_id
status_1
etag
1111
200
2023-09-29 09:00:01

2
somethin gelse
thing_1
c_id
status_1
etag
1111
200
2023-09-29 09:00:01

3
protocol://full/path/to/thing/thing_1.file2
thing_1
c_id
status_1
etag
1111
200
2023-09-29 09:00:02

4
protocol://full/path/to/thing/thing_2.file1
thing_2
c_id
status_1
etag
1111
200
2023-09-29 09:00:03

5
protocol://full/path/to/thing/thing_2.file2
thing_2
c_id
status_1
etag
1111
200
2023-09-29 09:00:04

6
protocol://full/path/to/thing/thing_2.file3
thing_2
c_id
status_1
etag
1111
200
2023-09-29 09:00:05

7
protocol://full/path/to/thing/thing_3.file1
thing_3
c_id
status_1
etag
1111
200
2023-09-29 09:00:06



SELECT 7


-- If you don't repeat the where clause in the update, you might update other rows.
-- This is slow and cumbersome and hard to maintain.
with these_things as (
  SELECT thing.id 
  FROM thing 
  WHERE thing.status = ('status_1') AND 
  thing.c_id = ('c_id') AND 
  thing.name LIKE ('protocol://full/path/to/thing/%')
  ORDER BY thing.status_1_date LIMIT (2)
)
UPDATE thing 
SET status = 'status_2'
from these_things
where thing.id = these_things.id
RETURNING *


id
name
thing_id
c_id
status
etag
last_modified
size
status_1_date
id


1
protocol://full/path/to/thing/thing_1.file1
thing_1
c_id
status_2
etag
1111
200
2023-09-29 09:00:01
1

3
protocol://full/path/to/thing/thing_1.file2
thing_1
c_id
status_2
etag
1111
200
2023-09-29 09:00:02
3



UPDATE 2

id	name	thing_id	c_id	status	etag	last_modified	size	status_1_date
1	protocol://full/path/to/thing/thing_1.file1	thing_1	c_id	status_1	etag	1111	200	2023-09-29 09:00:01
2	somethin gelse	thing_1	c_id	status_1	etag	1111	200	2023-09-29 09:00:01
3	protocol://full/path/to/thing/thing_1.file2	thing_1	c_id	status_1	etag	1111	200	2023-09-29 09:00:02
4	protocol://full/path/to/thing/thing_2.file1	thing_2	c_id	status_1	etag	1111	200	2023-09-29 09:00:03
5	protocol://full/path/to/thing/thing_2.file2	thing_2	c_id	status_1	etag	1111	200	2023-09-29 09:00:04
6	protocol://full/path/to/thing/thing_2.file3	thing_2	c_id	status_1	etag	1111	200	2023-09-29 09:00:05
7	protocol://full/path/to/thing/thing_3.file1	thing_3	c_id	status_1	etag	1111	200	2023-09-29 09:00:06