How to delete duplicate rows in Azure Synapse

user_18691717 0
Viewed 197

How can I delete duplicate rows from Azure Synapse Analytics? I'd like to delete one of the rows where audit_date = '2022-08-10' and city = 'LA'. I'd like to keep only 1 row. I've tried using the CTE method( Row_number()... ). Unfortunately, SQL Pool doesn't support Delete statements with CTE.

audit_date city number_of_toys number_of_balloons number_of_drinks
2022-08-10 LA 35 100 40
2022-08-10 NY 20 70 30
2022-08-10 LA 35 102 40
azure-sql-pool azure-synapse azure
1 Answers
  • You can do this using DELETE and ROW_NUMBER(). I have created a similar table with the sample data that you have given.

enter image description here


  • Now use the ROW_NUMBER() function to partition by audit_date and city based on your condition.
SELECT *, ROW_NUMBER() OVER (PARTITION BY audit_date,city ORDER BY audit_date,city) AS row_num FROM demo where audit_date='2022-08-10' and city='LA'

enter image description here


⦁ You can use the following query to complete the delete operation only on the rows where row_num > 1.

DELETE my_table FROM 
(
SELECT *, ROW_NUMBER() OVER (PARTITION BY audit_date,city ORDER BY audit_date,city) 
AS row_num FROM demo where audit_date='2022-08-10' and city='LA'
) my_table
where row_num>1

enter image description here


This way you can delete duplicate records by retaining one row using DELETE and ROW_NUMBER() as demonstrated above.

user_188445850
Related