CODEWITHSUNDEEP
sql
Chris Guettar
Chris Guettar

Reputation: 115

Is there a DBMS-indepent way to fill a given column with an incrementing/ascending number?

Lets say I have a table with a varchar colum and and integer column. The varchar columns are filled and the integer columns are all emtpy:

enter image description here

I would like to execute a signle SQL update statement which populates the integer column with an ascending number like this:

enter image description here

This update statement should not be DBMS-specific, it should run on all popular SQL DBMSs. Is there a way to do this?

I edited the contents of the varchar column as the previous version of my question implied that they were numbered and distinct. They are not - they are completely random.

Upvotes: 0

Views: 44

Answers (2)

Lukasz Szozda
Lukasz Szozda

Reputation: 175964

If My_Varchar_Column is unique you could use ROW_NUMBER:

UPDATE table_name
SET my_integer_column = 
        (SELECT rn
        FROM  (SELECT t.*, ROW_NUMBER() OVER(ORDER BY My_Varchar_Column) AS rn
               FROM table_name t) sub
        WHERE table_name.My_Varchar_Column = sub.My_Varchar_Column);

db<>fiddle demo

It will work on modern RDBMS like PostgreSQL/MySQL 8.0/Maria DB/SQLite 3.25/Oracle/SQL Server.

Upvotes: 0

Yogesh Sharma
Yogesh Sharma

Reputation: 50173

I would use subquery :

UPDATE t
     SET my_integer_column = (SELECT COUNT(*) FROM t t1 WHERE t1.My_Varchar_Column <= t.My_Varchar_Column);

Upvotes: 2

Related Questions