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 );