Mysql Question posted in
The official MySQL documentation can be found here.

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

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?

Tags:

3

Answers


  1. 0

    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

    Login or Signup to reply.
  2. 0

    You want to update the version of the current validated article with the next versions in ascending order?

    I think you don’t need to match on id and language, why would you since you have null on version on that moment. Instead just get the highest version overall and add 1.

    UPDATE article_contents
SET version=(
    SELECT TOP 1 version
    FROM article_contents
    ORDER BY 1 desc
) + 1
WHERE id=@id;

    I got it wrong, you want the versions for one specific article. Thank you jarlh for pointing it out.

    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;
    Login or Signup to reply.
  3. 0

    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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search