none
Delete duplicate records from Azure SQL DW- Doesn't work RRS feed

  • Question

  • I m trying to delete some dup records on Azure SQL DW.

    Tried following queries but nothing works/not supported on Azure DW platform

    1)With cte (select *,row_num from tablename) 

    delete cte from where row_num>1

    throws errorthat "delete statement cannot follow  cte"

    2)Delete statement using sub queries

    Delete <TableAlias> from

            (Select sub query)Tablealias where row_num>1

    Throws error "A From clause is not supported"

    So we can't use the delete statements on Azure DW?

    Alternate solution followed

    1. Create CTAS table. Loaded all data from original table
    2. Truncated orginal table
    3. loaded from ctas with rank=1


    Disclaimer: The contents, I write here are my personal views, not the view of my employer or anyone else.


    Friday, December 29, 2017 6:41 PM

All replies

  • I dunno which version you're on

    but as per this

    https://docs.microsoft.com/en-us/sql/t-sql/queries/with-common-table-expression-transact-sql

    CTEs seem to be supported in Azure DW


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Friday, December 29, 2017 6:46 PM
  • I checked that too. But i dont see a working sample available anywhere else.

    I dont know what you mean by version on Azure platform.

    I use the Azure SQL DW.


    Disclaimer: The contents, I write here are my personal views, not the view of my employer or anyone else.

    Friday, December 29, 2017 7:33 PM
  • Hi,

    You query is not correct, the from statement came before the table name, it should be like this :

    With cte (select *,row_num from tablename) 
    delete from cte where row_num>1
    Delete from <TableAlias> 
    (Select sub query)Tablealias where row_num>1



    Please mark as answered, If you feel happy with this answer.


    Friday, December 29, 2017 7:54 PM
  • I checked that too. But i dont see a working sample available anywhere else.

    I dont know what you mean by version on Azure platform.

    I use the Azure SQL DW.


    Disclaimer: The contents, I write here are my personal views, not the view of my employer or anyone else.

    you query should be like this

    With cte as (select *,row_number() over (partition by <columns> order by <columns>) as row_num from tablename) 
    
    delete from cte where row_num >1


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Friday, December 29, 2017 8:02 PM
  • of course, i tried the same

    --delete from cte_del_dups where rnk>1

    I may have missed the syntax while composing the question.


    Disclaimer: The contents, I write here are my personal views, not the view of my employer or anyone else.

    Saturday, December 30, 2017 12:17 AM
  • of course, i tried the same

    --delete from cte_del_dups where rnk>1

    I may have missed the syntax while composing the question.


    Disclaimer: The contents, I write here are my personal views, not the view of my employer or anyone else.

    please post the exact statement used

    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Saturday, December 30, 2017 6:59 AM
  • Hello,

    can you please post your actual query that you wrote.


    Please Mark as Answer if my post works for you or Vote as Helpful if it helps you. Kapil Singh

    Saturday, December 30, 2017 7:37 AM
  • I m trying to delete some dup records on Azure SQL DW.

    Tried following queries but nothing works/not supported on Azure DW platform

    1)With cte (select *,row_num from tablename) 

    delete cte from where row_num>1

    throws errorthat "delete statement cannot follow  cte"

    2)Delete statement using sub queries

    Delete <TableAlias> from

            (Select sub query)Tablealias where row_num>1

    Throws error "A From clause is not supported"

    So we can't use the delete statements on Azure DW?

    Alternate solution followed

    1. Create CTAS table. Loaded all data from original table
    2. Truncated orginal table
    3. loaded from ctas with rank=1


    Disclaimer: The contents, I write here are my personal views, not the view of my employer or anyone else.


    Hi,

    Have you found a solution ?

    Best regards,

    NSG

    Monday, January 14, 2019 3:14 PM
  • I also am in azure sql dw and got the error when using the code above

    Parse error at line: 1, column: 12: Incorrect syntax near 'select'.

    Tuesday, July 2, 2019 8:15 PM
  • I am in azure sql dw and still get the following error

    Common table expressions followed by INSERT, UPDATE, DELETE, or MERGE are not supported in this version.

    when using the code:

    With cte as (select *,row_number() over (partition by datekey order by datekey) as row_num from dim.FullCalendar) 

    delete from cte where row_num >1

    has anyone found a solution?

    Tuesday, July 2, 2019 8:17 PM