Script for TSQL-CodePudding

Please help me write a Transact-SQL script.

There is a table with conditional columns PK_ID (Number), FK_ID (Number) and SEQ (Number):

PK_ID is always unique.
Several PK_IDs can be assigned to one FK_ID.
SEQ is the sequence number for the PK_ID, in order of priority assignment to the FK_ID.

I just can’t write a script that would change the entries in SEQ from 1 to the sequence number of the last FK_ID entry in the PK_ID context, provided that initially all SEQ = 1.

What is now (Example):

| PK_ID|  FK_ID | SEQ |
| ---- | ------ |  -  |
| 121  | 18821  |  1  |
| 358  | 18821  |  1  |
| 611  | 18821  |  1  |
| 7251 | 23357  |  1  |
| 95   | 23357  |  1  |
| 922  | 15511  |  1  |
| 1    | 53810  |  1  |
| 777  | 53810  |  1  |
| 953  | 53810  |  1  |
| 120  | 53810  |  1  |

Result:

| PK_ID|  FK_ID | SEQ |
| ---- | ------ |  -  |
| 121  | 18821  |  1  |
| 358  | 18821  |  2  |
| 611  | 18821  |  3  |
| 7251 | 23357  |  1  |
| 95   | 23357  |  2  |
| 922  | 15511  |  1  |
| 1    | 53810  |  1  |
| 777  | 53810  |  2  |
| 953  | 53810  |  3  |
| 120  | 53810  |  4  |

Please help me and thanks in advance.

CodePudding user response：

Assuming you want to update your table.

Note: The window functions are well worth your time getting comfortable with.

Example or