Create table and simultaneously replace coulmn contents with row number

Question

I have the following statement:

CREATE TABLE result2 AS
  SELECT t.* 
  FROM sample1 as t
  CROSS JOIN (select * from range(0,10)) v(i);
select * from result2;

and I'd like to have in the created table (result2), which contains the column 'id' from sample1, that the corresponding 'id' column has instead the row number. I'd prefer to have this OTF rather than create a new table, then manipulate this new table using say UPDATE and SET. How can I achieve this?

Answer 1

Create table and simultaneously replace column contents with row number

To Update it while creating table we have to select first row number and then the remaining columns of respective table.

CREATE TABLE result4 AS 
SELECT ROW_NUMBER() OVER (ORDER BY Id) AS Id, Name,.......(columns you want)
FROM y2;

Sample table and execution:

You can't Update the table with reference to another table column will throw you an error.