Re: update records to have occurance number

> On 25 Oct 2016, at 17:06, Mark Lybarger <mlybarger(at)gmail(dot)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 to update 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.