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 IS NULL
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
  LEFT JOIN `tests` AS test ON test.author_id = author.id
SET author_status_id = (SELECT id
                        FROM author_statuses
                        WHERE author_statuses.status = 'Registered')
WHERE test.id IS NULL AND author_status_id = (SELECT id
                                              FROM author_statuses
                                              WHERE author_statuses.status = 'Test Delivery Failed');

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
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
SET author_status_id = (SELECT id
                        FROM author_statuses
                        WHERE author_statuses.status = 'Rejected');
