How to update column with ascending value column

Question

I would like to add an order column wich will be position of my record. Positions are connected with name. If the name is S1 - order should be 1, for S2 - order should equal 2 etc. How can I do this?

Real state:

id name position
------- ------- -------
1 S4 null
2 S2 null
3 S3 null
4 S1 null 

Expected:

id name position
------- ------- -------
1 S4 4
2 S2 2
3 S3 3
4 S1 1

I tried with update:

WITH x AS
(
 SELECT
 id,
 name,
 ROW_NUMBER() OVER (ORDER BY name) position
 FROM
 stage
)
UPDATE
 stage
SET
 position = x.position
FROM
 x
WHERE
 x.id = stage.id;

Stage is the name of the table.

• Lennart - Slava Ukraini: Should order be the number in name, or the relative position among names? I.e. for name S1, S2, S5, would order be 1,2,3 or 1,2,5?
• Bob: Yes, an order should relative to name. If S1 - order 1, if S2 - order 2
• Lennart - Slava Ukraini: In my example, S1, S2, S5, what would order be?
• Bob: Should be 1, 2 and 5

Answer 1

You can use ROW_NUMBER() window function:

WITH x AS
(
 SELECT
 id,
 name,
 ROW_NUMBER() OVER (ORDER BY name) ord
 FROM
 tbl
)
UPDATE
 tbl
SET
 ord = x.ord
FROM
 x
WHERE
 x.id = tbl.id;

SELECT * FROM tbl;

id | name | ord
-: | :--- | --:
 1 | S4 | 4
 2 | S2 | 2
 3 | S3 | 3
 4 | S1 | 1

db<>fiddle here

If there isn't duplicated names you can remove 'S' from name:

UPDATE
 tbl
SET
 position = right(name, length(name) - 1)::int

db<>fiddle here

• Bob: Position can not be same. How to avoid adding records with same position?
• McNets: Please update your question by adding a real example of your data and the expected result.
• Lennart - Slava Ukraini: @Bob, row_number() will never repeat the same number per partition (there's only one partition here)
• Bob: I added real state and expected one. Your select doesn't update my table. I need to use sequence I guess to update orders number and avoid adding another record with same position.
• Bob: @McNets I had an error ERROR: syntax error in or close "FROM" LINE 7: FROM ^ SQL state: 42601 Character: 92 I change table name.

Answer 2

If all you want is everything after the letter S, something like:

update tbl 
 set ord = substr(name,2)::int;

should do. Question is why you have to have a column for this, can't you just order by:

select * from tbl order by substr(name,2)::int;

It is good practice to not duplicate data unless there's a good reason for it.

• Bob: I have simple names now but it is going to change in the future so I need position column to show what was first, second, third etc..