UPDATE, subquery with SELECT TOP I wrote this query:

UPDATE authors
SET best_seller =
    (
    SELECT TOP 1 book_id
    FROM books
    WHERE books.author_id=authors.author_id
    ORDER BY copies_sold DESC
    )


I would like the SELECT to be evaluated once for every row in books, so that every author record contains his/her most sold book. Let's ignore:


Performance
The bad idea of storing something that could be computed in other ways
The case that there is a tie between 2 books of the same author


Do you see any other problem with the query, or will it work as expected in all cases?
I don't see any issues with your query, regarding correctness of the update.

Another way to get the same results, using window functions.

Regarding efficiency, you'll need to check execution plans and test both methods.

UPDATE a
SET a.best_seller = b.book_id
FROM authors AS a
  JOIN
  ( SELECT author_id, book_id,
           rn = ROW_NUMBER() OVER (PARTITION BY author_id
                                   ORDER BY copies_sold DESC)
    FROM books
  ) AS b
  ON  b.author_id = a.author_id
  AND b.rn = 1 ;