How to update column with ascending value column
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&lt;>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&lt;>fiddle here