PostgreSQL Expert needed for large batch update

I have a test table called names with about 1 billion records of fake names of people (first_name and last_name).

I created a table called last_name_distinct in which I have each last name with the number of records it has in the names table stored in the field last_name_distict.count. To populate last_name_distict from the names table took about 30 minutes and resulted in about 20 million unique last names.

In the names table I also have a field called page. This is a page number which will be used to display the names. I want to display the names for a specific last_name over multiple pages, with a maximum of 100 records per page. So if the last name "Doe" has 150 records, it will display over 2 pages, 100 on the first page and 50 on the second page.

So to do this I asked ChatGPT to make an sql script (see below) to loop on the last_name_distinct table and then loop on the names table for the current last_name and update the page field with a page number for each record in names.

But the process for doing this takes way too long, about 1 month to run, and I am using a very fast multi-core dedicated server.

I tried using both the sql script provided by ChatGPT and writing my own program in php to do the same job, but both run too long.

I am guessing that the most efficient way of doing this is to run an sql script from within psql (\i script.sql), which is what I did with the scipt ChatGPT made for me.

But I am wondering if there is a faster way of doing this? Maybe there is someone out there who is a PostgeSQL expert and nows a trick or two to make this run faster? If you have the solution please let me know.

DO $$
DECLARE
name_row record;
counter integer := 0;
record_count integer := 0;
record_row names%ROWTYPE; -- Declare a record variable to hold the values from the names table
BEGIN
-- Loop through distinct last names where count > 100
FOR name_row IN (SELECT last_name, count FROM last_name_distinct WHERE count > 100) LOOP
-- Reset the counter for each distinct last name
counter := 0;

-- Retrieve records from names table matching the current last name, sorted by last name and first name
FOR record_row IN (SELECT * FROM names WHERE last_name = name_row.last_name ORDER BY last_name, first_name) LOOP
-- Increment the counter for each record
counter := counter + 1;

-- Calculate the page number
record_row.page := (counter - 1) / 100 + 1;

-- Update the page field in the names table using the name_id
UPDATE names SET page = record_row.page WHERE name_id = record_row.name_id;

-- Print the last_name_distinct.last_name field for every 100,000th record processed
record_count := record_count + 1;
IF record_count % 100000 = 0 THEN
RAISE NOTICE 'Processing last name: %', name_row.last_name;
END IF;
END LOOP;
END LOOP;
END $$;