UPDATE authors AS author
  JOIN `user` AS user ON user.id = author.user_id
  JOIN `tests` AS test ON test.author_id = author.id AND test.status = 1
  JOIN max_test_id AS t4 ON t4.test_id = test.id
SET author_status_id = (SELECT id
                        FROM author_statuses
                        WHERE author_statuses.status = 'Accepted');

UPDATE authors AS author
  JOIN `user` AS user ON user.id = author.user_id
  JOIN `tests` AS test ON test.author_id = author.id AND test.status = 0
  JOIN max_test_id AS t4 ON t4.test_id = test.id
SET author_status_id = (SELECT id
                        FROM author_statuses
                        WHERE author_statuses.status = 'Rejected');

UPDATE authors AS author
  JOIN `user` AS user ON user.id = author.user_id
  JOIN `tests` AS test ON test.author_id = author.id
  LEFT JOIN `themes` AS theme ON theme.id = test.theme_id
SET author_status_id = (SELECT id
                        FROM author_statuses
                        WHERE author_statuses.status = 'Test Delivered')
WHERE author_status_id IS NULL OR author_status_id = (SELECT id
                                                      FROM author_statuses
                                                      WHERE author_statuses.status = 'Registered');

UPDATE authors AS author
  JOIN `user` AS user ON user.id = author.user_id
  JOIN `tests` AS test ON test.author_id = author.id
  LEFT JOIN `themes` AS theme ON theme.id = test.theme_id
SET author_status_id = (SELECT id
                        FROM author_statuses
                        WHERE author_statuses.status = 'Test Delivery Failed')
WHERE test.date_end IS NULL;