Search code examples
postgresqlmultiple-columnsdbeaver

PostgreSQL: How to Enter/Update data in a column for each unique customer and increment it by 1 until the end of Table

I ask you to help me create a query (or Script) in the database (PostgreSQL). I want to write values in the "number" column, and so that it increases by 1 value for each unique client. I've searched all t he data on the Internet, but I still can't figure out how to do it right?

My db looks like this:

id number client_id amount
193079 NULL 001 100900.00
193080 NULL 002 81900.00
193081 NULL 003 76400.00
193082 NULL 004 64800.00
193083 NULL 001 51200.00
193084 NULL 002 111348.57
193085 NULL 002 159962.87
193086 NULL 003 379492.86
193087 NULL 004 223358.30
193088 NULL 003 379492.86
193089 NULL 001 465358.30
193090 NULL 003 125358.30

What I want is to wryte single "Update" query or script that will modity my table like this:

id number client_id amount
193079 1 001 100900.00
193080 1 002 81900.00
193081 1 003 76400.00
193082 1 004 64800.00
193083 2 001 51200.00
193084 2 002 111348.57
193085 3 002 159962.87
193086 2 003 379492.86
193087 2 004 223358.30
193088 3 003 379492.86
193089 4 001 465358.30
193090 4 003 125358.30

I don't quite understand, should I use sequence and nextval functinality/correctly? Even If, I can/must use "sequence and nextval", I don't know how to use them properly! Please help me!

I am using DBeaver to connect into my DB. I tried several types of Update:

UPDATE "myDatabase".myTable SET "number" =(CASE WHEN client = 001 THEN nextval(1) WHEN client = 002 THEN nextval(1) ... CASE WHEN number is not null THEN skip ELSE 'skip this row' END END );

Solution

  • If you want this as a one-off update, use row_number() window function and set up the window to be partitioned by client_id: demo

    with cte as (select id,row_number()over w1 as number
             from your_table
             window w1 as (partition by client_id order by id) )
update your_table this
set number=cte.number
from cte
where this.id=cte.id;

select * from your_table order by id;
    id number client_id amount
    193079 1 001 100900.00
    193080 1 002 81900.00
    193081 1 003 76400.00
    193082 1 004 64800.00
    193083 2 001 51200.00
    193084 2 002 111348.57
    193085 3 002 159962.87
    193086 2 003 379492.86
    193087 2 004 223358.30
    193088 3 003 379492.86
    193089 this should be 3 001 this is the 3rd 001 465358.30
    193090 4 003 125358.30

    Note the difference on your second row from the bottom: I think you wanted 3 in there.