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

Question

Hi,

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 )
DECLARE @CurrentRow INT
WHILE @Counter  <= @MaxCounter 
BEGIN 
      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;
END;

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.

Answer 1

Hello @Shreyash Choudhary !

Your queries have some errors

Please try :

WITH Numbered AS (
    SELECT 
        serial_no,
        ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS new_serial_no 
    FROM 
        table_name 
    WHERE 
        serial_no IS NULL
)
UPDATE 
    Numbered
SET 
    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!

Regards