How can I update using information from the main query in a subquery?

Question

I handle articles on a multi-language site, with the possibility for the users to update the content. I use a MySQL database.

Table articles: id

Table article_contents: id article_id (FK) content lang version

If the new content is still a draft, version is null. As soon as the draft is validated by a moderator, it becomes a definitive version and takes the next available version for the given article_id and the given lang.

UPDATE article_contents
SET version=(
    SELECT MAX(version)
    FROM article_contents
    WHERE article_id=SAME_AS_THE_ONE_IN_THE_MAIN_QUERY
    AND lang=SAME_AS_THE_ONE_IN_THE_MAIN_QUERY
) + 1
WHERE id=:id; 

By what can I replace SAME_AS_THE_ONE_IN_THE_MAIN_QUERY, or is there a cleaner way to do it?

Answer 1

If you are fine with using variables, try smt like this (T-SQL):

begin
 declare @v_test NVARCHAR(MAX)
 declare @v_lang NVARCHAR(MAX)

-- IDK where id/lang comes from, so I'm just setting them like this
 set @v_test = 'VC000033'
 set @v_lang = 'eng'

    update [your table]
    set [your column] = (
        select max([your column]) + 1
        from [your table]
        where No_ = @v_test
        and lang = @v_lang)
    where No_ = @v_test
end

Depending on the operations you are performing, this might not be the best solution performance wise. For that you might wanna try CTE

Answer 2

   UPDATE ac  
   SET version=(
        SELECT TOP 1 ISNULL(version,0)
        FROM article_contents ac2
        WHERE ac2.article_id  = ac.article_id 
            AND ac2.lang = ac.lang
        ORDER BY 1 desc
    ) + 1
    FROM article_contents ac
    WHERE id=@id; 

Answer 3

Use a correlated subquery, i.e. let it reference the update table:

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 = :id;

Demo: https://dbfiddle.uk/IkH8jGmy

Answer 4

See example

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=@id; 

In your query, I think, main problem is You can't specify target table 'article_contents' for update in FROM clause

fiddle