Thread: update records to have occurance number

> On 25 Oct 2016, at 17:06, Mark Lybarger <mlybarger@gmail.com> wrote:
>
> I want to update a table to have the value of the occurrence number.  For instance, I have the below table.  I want
toupdate the number column to increment the count of last name occurrences, so that it looks like this: 
>
> first    last    1
> second    last    2
> third    last    3
> first    other    1
> next    other     2
>
> Here's my simple table:
>
> create table person ( fname text, lname text, number integer);
>
> insert into person (fname, lname) values ('first', 'last');
> insert into person (fname, lname) values ('second', 'last');
> insert into person (fname, lname) values ('third', 'last');
>
> insert into person (fname, lname) values ('first', 'other');
> insert into person (fname, lname) values ('next', 'other');
>
> How would I issue an update statement to update the number column?

That depends on which order you want the database to perceive those rows in.
The above example suggests that alphabetical order on fname might work, in which case:

update person set number = count(p2.fname) +1
  from person p2
 where p2.lname = person.lname
   and p2.fname < person.fname;

Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.