Update stored records numbers depending on previous rows

Question

I have to create script that will set the column of one table in databse based on these conditions:

• Every Selling order (table selling_order) has record in Selling order person (table selling_order_person) where is email of person stored

• I have to order all records in selling_order by order_time ASC (so the oldest orders will be first) and on these orders I have to set the column of selling_order name rank (that is column repesents how many orders customer alredy did. Its based on email in selling_order_person). So the first order of customer will have 1, the second one will have 2 etc. And this have to be done for one customer.

• All this logic will be stored in liquibase (so it will run just once, the next records will be managed by Java on the row creation. But the old data have to be managed first via liquibase).

I´ve written this procedure. But it does not work, It just set number 1 to half of a table (it is possible cause its testing table so there could be cases where selling_order_person have no mail or no record). Can anyone help me with that? Its my first SQL procedure so I dont know whats wrong.

CREATE OR REPLACE FUNCTION updateSellingOrderCustomerCount()
    RETURNS VOID AS
$$
DECLARE
    t_curs CURSOR FOR SELECT so.*, sop.email
                      FROM selling_order so
                               INNER JOIN selling_order_person sop on so.person_id = sop.id
                      ORDER BY order_time;
BEGIN
    FOR tmp IN t_curs
        LOOP
            UPDATE selling_order
            SET rank =
                    (SELECT COALESCE(MAX(so.rank), 1)
                     FROM selling_order so
                              INNER JOIN selling_order_person sop on so.person_id = sop.id
                     WHERE sop.email = tmp.email)
            WHERE selling_order.id = tmp.id;
        END LOOP;
END
$$ LANGUAGE plpgsql VOLATILE;

SELECT * FROM updateSellingOrderCustomerCount();

For simplicity Ive deleted all columns that we dont work with now.

create table selling_order
(
    id bigserial not null
        constraint selling_order_pkey
            primary key,
    selling_order_person_id bigint not null,
    rank int,
    order_time timestamp
);

create index idx_selling_order_person_id
    on selling_order (person_id);


create table selling_order_person
(
    id bigserial not null
        constraint pk_selling_order_person
            primary key,
    email varchar(64)
);

Answer 1

Big thanks to a_horse_with_no_name. Ive just editted a little his code and it works like charm.

UPDATE selling_order so
    SET rank = tmp.rnk
    FROM (
             SELECT so.id, sop.email,
                    ROW_NUMBER() OVER (PARTITION BY LOWER(sop.email) ORDER BY order_time) AS rnk
             FROM selling_order so
                      INNER JOIN selling_order_person sop ON so.selling_order_person_id = sop.id
         ) tmp
    WHERE tmp.id = so.id;

Answer 2

As far as I can tell, this can be done with a single UPDATE statement

update selling_order so
  set rank = t.rnk
from (
  select id, 
         row_number() over (partition by selling_order_person_id order by order_time) as rnk
  from selling_order
) t
where t.id = so.id;

I assume that the customer you mention in "how many orders customer already did" refers to selling_order_person_id not to some hidden "customer" column.

Online example: https://rextester.com/ATD51610

---

But managing this rank column seems pretty useless, given the fact that you can easily calculate that information (as shown in the inner SELECT of the UPDATE statement)