No, you run a SELECT … LIMIT in a CTE, and the main query is an UPDATE … JOIN CTE ON mytable.id = CTE.id

Would you not just be able to do the CTE as you would a correlated sub query? Something like:

    WITH batch AS (
      SELECT id FROM user_profile
      WHERE followers_count IS NULL
      LIMIT 10000
    )
    UPDATE user_profile
    SET followers_count = 0
    FROM batch
    WHERE user_profile.id = batch.id

But with the difference that if you didn’t want to round-trip to the application for each batch you could now make this a recursive CTE?