How to update column with ascending value column 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.
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