Postgresql Update Table row values by Grouped Column First of all a little bit of background:

I currently have a table which contains call data both inbound and outbound from my business, up until recently there was no api_id data available, however this has come in with the last update to my suppliers API, as such i have some historical call data that can be grouped by call_id.

What i am looking to do is update the existing rows in the database that do not have an api_id with a random string of characters grouping them by their call_id.


| ... | call_id | api_id |
|------------------------|
| ... | 123     | AAA    |
| ... | 456     |        |
| ... | 456     |        |
| ... | 788     |        |
| ... | 789     |        |
| ... | 789     |        |


Would then become:


| ... | call_id | api_id |
|------------------------|
| ... | 123     | AAA    |
| ... | 456     | GHA    |
| ... | 456     | GHA    |
| ... | 788     | ZPM    |
| ... | 789     | LFF    |
| ... | 789     | LFF    |


Effectively an update by grouped query, which i have done a search for but was unable to find anything.

If anyone here could help or point me in the right direction it would be much appreciated.

Database Technology: Postgresql 9.5.4
Simple answer - as the dummy value in api_id doesn't matter: use the call_id:

update the_table 
    set api_id = call_id::text 
where api_id is null;




Complicated answer: 

Assuming you have some way of generating a random string, you could do something like this (untested):

update the_table
   set api_id = t.random_string
from (
  select distinct on (call_id) call_id, random_string() as random_string
  from the_table
  where api_id is null
  order by call_id
) t 
where t.call_id = the_table.call_id
 and the_table.api_id is null;


This generates one row with per call_id in the inner query and generates a random value for each of that. This result is then used to update the target table.

The above assumes that if there is already value for a specific call_id there is one for all rows. It won't "fill" in missing api_id for other rows (e.g. if there was a row with call_id = 123 but no api_id)