Updating a table with row number in postgresql

Question

Trying to update a table with a unique iD column as there is no unique key column in the same.

When using the below CTE, I am getting an error of relation does not exist for the CTE name in the update statement. but the same is working fine when select statement is executed.

Query used:

With updateid As
(
SELECT 
ID,
ROW_NUMBER() OVER (ORDER BY Model DESC) AS RN
FROM aud
)UPDATE updateid SET ID='AUD'||repeat('0',5-length(cast(RN as varchar)))||cast(RN as varchar)

Error encountered:

ERROR:  relation "updateid" does not exist
LINE 7: )UPDATE updateid SET ID='AUD'+replicate('0',5-len(cast(RN as...
                ^
SQL state: 42P01
Character: 95

The select statement that worked well:

With updateid As
(
SELECT 
ID,
ROW_NUMBER() OVER (ORDER BY Model DESC) AS RN
FROM aud
)Select * from updateid

Answer 1

If you still want to update the table from some source result set, you can use update ... from ... with join condition specified in where clause:

create table t
as
select q, null::int as w
from unnest(array[1,2,3]) as q

with s as (
  select row_number() over(order by q desc) as rn
    , ctid as row_id
  from t
)
update t
set w = rn
from s
where t.ctid = s.row_id

select *
from t

 q |  w
-: | -:
 3 |  1
 2 |  2
 1 |  3

db<>fiddle here