Postgresql Update Table row values by Grouped Column

Question

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

• user1822: if that can really be a random string (i.e. the contents doesn't matter), then why not just use the call_id? update the_table set api_id = call_id::text where api_id is null?
• Matt Nunn: ..... Wow, i was so tied up in grouping on the update i didnt even think about that, just for future reference, is it possible to do what the question is asking however?

Answer 1

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)

• ypercubeᵀᴹ: Why distinct on (call_id) and not the simple group by call_id?
• ypercubeᵀᴹ: not sure what you mean. select a, random() from t group by a; works fine in my machine.
• user1822: @ypercubeTM: hmm, you are right. But I don't think it would make a performance difference