PostgreSQL and pl/pgsql SYNTAX to update fields based on SELECT and FUNCTION (while loop, DISTINCT COUNT)

Question

I have a large database, that I want to do some logic to update new fields.

The primary key is "id" for the table harvard_assignees

The LOGIC GOES LIKE THIS

1. Select all of the records based on "id"
2. For each record (WHILE), if (state is NOT NULL && country is NULL), update country_out = "US" ELSE update country_out=country

I see step 1 as a PostgreSQL query and step 2 as a function. Just trying to figure out the easiest way to implement natively with the exact syntax.

==== The second function is a little more interesting, requiring (I believe) DISTINCT:

1. Find all DISTINCT foreign_keys (a bivariate key of pat_type,patent)
2. Count Records that contain that value (e.g., n=3 records have fkey "D","388585")
3. Update those 3 records to identify percent as 1/n (e.g., UPDATE 3 records, set percent = 1/3)

Thanks in advance!

monte

{x:

Answer 1

For the first one:

UPDATE
     harvard_assignees
SET
    country_out = (CASE
                      WHEN (state is NOT NULL AND country is NULL) THEN 'US'
                      ELSE country
                  END);

At first it had condition "id = ..." but I removed that because I believe you actually want to update all records.

And for the second one:

UPDATE           
    example_table
SET              
    percent = (SELECT 1/cnt FROM (SELECT count(*) AS cnt FROM example_table AS x WHERE x.fn_key_1 = example_table.fn_key_1 AND x.fn_key_2 = example_table.fn_key_2) AS tmp WHERE cnt > 0)

That one will be kind of slow though.

I'm thinking on a solution based on window functions, you may want to explore those too.