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.

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

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.