MySQL 8.0

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 text
   ,lang varchar(10), version int);
insert into article_contents values
 (1,10,'content 10','EN',1)
,(2,11,'content 11','EN',null)
;
select * from article_contents;

Records: 2  Duplicates: 0  Warnings: 0




id
article_id
content
lang
version


1
10
content 10
EN
1

2
11
content 11
EN
null


UPDATE article_contents ac
   ,(
    SELECT article_id,lang,MAX(version) maxversion
    FROM article_contents ac2
    where ac2.article_id=@A_Id
) t
  SET version= coalesce(t.maxversion,0)+ 1
WHERE ac.article_id=t.article_id and ac.lang=t.lang
  and ac.id=@Id; 
select * from article_contents;

In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'fiddle.ac2.article_id'; this is incompatible with sql_mode=only_full_group_by


UPDATE article_contents ac
   ,(
    SELECT article_id,lang,MAX(version) maxversion
    FROM article_contents
    group by article_id,lang
) t
  SET version= coalesce(t.maxversion,0)+ 1
WHERE ac.article_id=t.article_id and ac.lang=t.lang
  and ac.id=2; 
select * from article_contents;

Rows matched: 1  Changed: 1  Warnings: 0




id
article_id
content
lang
version


1
10
content 10
EN
1

2
11
content 11
EN
1


UPDATE article_contents
   ,(
    SELECT article_id,lang,MAX(version) maxversion
    FROM article_contents
    group by article_id,lang
) t
  SET version= coalesce(t.maxversion,0)+ 1
WHERE article_contents.article_id=t.article_id and article_contents.lang=t.lang
  and id=2; 
select * from article_contents;

Rows matched: 1  Changed: 1  Warnings: 0




id
article_id
content
lang
version


1
10
content 10
EN
1

2
11
content 11
EN
2


UPDATE article_contents
SET version=(
    SELECT MAX(version)
    FROM article_contents
    WHERE article_id=10
    AND lang='EN'
) + 1
WHERE id=1; 

You can't specify target table 'article_contents' for update in FROM clause