0

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

  • 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? – a_horse_with_no_name Oct 24 '16 at 13:57
  • ..... 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? – Matt Nunn Oct 24 '16 at 14:04
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)

  • Why distinct on (call_id) and not the simple group by call_id? – ypercubeᵀᴹ Oct 24 '16 at 14:36
  • not sure what you mean. select a, random() from t group by a; works fine in my machine. – ypercubeᵀᴹ Oct 24 '16 at 14:56
  • @ypercubeᵀᴹ: hmm, you are right. But I don't think it would make a performance difference – a_horse_with_no_name Oct 24 '16 at 15:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.