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
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
DECLARE @Counter INT=1
DECLARE @MaxCounter INT=(SELECT MAX(serial_no) FROM table_name )
DECLARE @CurrentRow INT
WHILE @Counter <= @MaxCounter
SET @CurrentRow = (SELECT MIN(serial_no) FROM table_name WHERE serial_no=@Counter );
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.