How to update a column with the max value?

Question

MySQL

How to update salt_quests column with max value?
Update conditionally found 2 records, you need to find the maximum value in the salt_quests column for all records. Tried with subquery but gives error (cannot use repository_quests table in subquery)

UPDATE repository_quests 
  SET status_game_quests = 1, status_progress_registration_quests = 0, 
    salt_quests = (SELECT MAX(salt_quests) + 1 FROM repository_quests) 
    WHERE status_accessibility_quests = 1 
        AND status_active_quests = 1 
        AND status_game_quests = 0 
        AND status_progress_registration_quests = 1 
        AND NOW() >= registration_date_quests;

Answer 1

You have run into subquery limitations of the dml (update) command;
You need to first calculate the maximum value through a preliminary request, and then use this value as a variable.
Decision:

@salt_quest = SELECT MAX(salt_quests) + 1 FROM repository_quests;
UPDATE repository_quests SET status_game_quests = 1, status_progress_registration_quests = 0, salt_quests = @salt_quest
    WHERE status_accessibility_quests = 1 
        AND status_active_quests = 1 
        AND status_game_quests = 0 
        AND status_progress_registration_quests = 1 
        AND NOW() >= registration_date_quests;