CODEWITHSUNDEEP
sqldynamicrownum
Phil
Phil

Reputation: 41

SQL Server: dynamic RowNumber update of a table

I m trying to update all rows of my database with a RowNumber ...

In my loop running through my tables I got these two dynamic statements:

Set @SqlStringOne = ('
            Alter Table ' +@Schema+'.'+@Table+'
            Add RowID int;
            ')
    Exec(@SqlStringOne);

(is working and adding a new RowID (RowNum) Column into my table) and 

Set @SqlStringTwo = ('
            update ' +@Schema+'.'+@Table+'
            Set  x.RowID = x.RowID_x
            from
            (select 
             RowID,
             ROW_NUMBER() OVER 
            (ORDER BY (Select TOP 1 COLUMN_NAME from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE where TABLE_NAME = '+@Table+')) as RowID_x
            from '+@Schema+'.'+@Table+') x
            ');
    Exec dbo.sp_executesql @stmt = @SqlStringTwo

But the update is not working ...

Thanks in advance

Upvotes: 2

Views: 1222

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

I don't understand the order by. Why not just do this?

update x
    set x.RowID = x.RowID_x
    from (select  t.*,
                  row_number() over (order by (select null)) as rowid_x
          from ' + @Schema + '.' + @Table + '
         ) x;

You can use x in the update. I don't understand the reference to the system table for the order by. It should be returning a constant, so you might as well use (select null).

Upvotes: 1

PSK
PSK

Reputation: 17943

You have two issues in your query.

1- When you want to update using FROM, you need to use alias.

2- in your order by, you need to pass table name as a string.

Change your query like following.

Set @SqlStringTwo = ('
            update x 
            Set  x.RowID = x.RowID_x
            from
            (select 
             RowID,
             ROW_NUMBER() OVER 
            (ORDER BY (Select TOP 1 COLUMN_NAME from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE where TABLE_NAME = '''+@Table+''')) as RowID_x
            from '+@Schema+'.'+@Table+') x
            ');
    Exec dbo.sp_executesql @stmt = @SqlStringTwo

You can always print your sql query to check for any issues.

Upvotes: 0

Related Questions