Azure Synapse Analytics Dedicated SQL pool table column value : genrate sno.

Shreyash Choudhary 126 Reputation points


I have a synapse dedicated sql pool table which contains 2 million records. i have a clumn serial_no which is null currently , i want to insert/update value in this column assigning each row with a number in sequentialy order (1,2,3...) .i have query for the same but not updating value in the column ,running succesfully

--query 1

UPDATE table_name
SET serial_no=new_serial_no
SELECT serial_no,ROW_NUMBER() OVER(ORDER BY serial_no) AS new_serial_no FROM table_name
) AS updated_rows
WHERE table_name.serial_no =updated_rows.serial_no

--query 2

DECLARE @Counter INT=1
DECLARE @MaxCounter INT=(SELECT MAX(serial_no) FROM table_name )
WHILE @Counter  <= @MaxCounter 
      SET @CurrentRow = (SELECT MIN(serial_no)  FROM table_name WHERE serial_no=@Counter  );
      UPDATE table_name 
      SET serial_no=@Counter  
     WHERE serial_no=@CurrentRow
 SET @Counter = @Counter+1;

Both query run succesfully but not updating the table column values!!!

why i needed this? coz i want to create batches and load the batched data into crm

please provide efficient solution if anyone know.

Thanks in advance.

Azure SQL Database
Azure Synapse Analytics
Azure Synapse Analytics
An Azure analytics service that brings together data integration, enterprise data warehousing, and big data analytics. Previously known as Azure SQL Data Warehouse.
3,547 questions
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
10,958 questions
Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
8,007 questions
0 comments No comments
{count} votes

Accepted answer
  1. Konstantinos Passadis 11,936 Reputation points

    Hello @Shreyash Choudhary !

    Your queries have some errors

    Please try :

    WITH Numbered AS (
            ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS new_serial_no 
            serial_no IS NULL
        serial_no = new_serial_no;

    This is a Common Table Expression (CTE) called Numbered to generate row numbers for each row in table_name where serial_no is NULL.

    Please try this and return your feedback !

    I hope this helps!

    Kindly mark the answer as Accepted and Upvote in case it helped!


    1 person found this answer helpful.

0 additional answers

Sort by: Most helpful