Postgres 16

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

create table article_contents
  (id int, article_id int, content varchar(10), lang int, version int);

CREATE TABLE


insert into article_contents values
   (1, 1, 'text', 1, 1),
   (2, 1, 'text', 2, 1),
   (3, 2, 'text', 3, 1),
   (4, 3, 'text', 1, 2);

INSERT 0 4


select * from article_contents order by id;


id
article_id
content
lang
version


1
1
text
1
1

2
1
text
2
1

3
2
text
3
1

4
3
text
1
2



SELECT 4


update article_contents ac
set version = coalesce((select max(version)
                        from article_contents
                        where article_id = ac.article_id
                          and lang = ac.lang), 0) + 1
where id = 2;

UPDATE 1


select * from article_contents order by id;


id
article_id
content
lang
version


1
1
text
1
1

2
1
text
2
2

3
2
text
3
1

4
3
text
1
2



SELECT 4