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

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    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

  • Question
    Sign in to vote
    0
    Sign in to vote

    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
  • Question
    Sign in to vote
    0
    Sign in to vote

    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
  • Question
    Sign in to vote
    0
    Sign in to vote

    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
  • Question
    Sign in to vote
    0
    Sign in to vote

    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
  • Question
    Sign in to vote
    0
    Sign in to vote

    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
  • Question
    Sign in to vote
    0
    Sign in to vote

    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
  • Question
    Sign in to vote
    0
    Sign in to vote

    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
  • Question
    Sign in to vote
    0
    Sign in to vote

    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
  • Question
    Sign in to vote
    0
    Sign in to vote

    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
  • Question
    Sign in to vote
    0
    Sign in to vote

    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
  • Question
    Sign in to vote
    0
    Sign in to vote
    I have the same problem, Microsoft Documentation is slightly confusing in terms of Azure Data Warehouse:

    (unable to paste a link due to verification issues)

    At first it seems it is supported as it states: A CTE must be followed by a single SELECT, INSERT, UPDATE, or DELETE statement that references some or all the CTE columns. A CTE can also be specified in a CREATE VIEW statement as part of the defining SELECT statement of the view.

    But under Features and limitations of Azure Data warehouse it states: A CTE must be followed by a single SELECT statement. INSERT, UPDATE, DELETE, and MERGE statements are not supported

    I have found a messy workaround where I load all except the duplicates and then rename to the original table. Still working on a better solution.


    Monday, September 30, 2019 3:10 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Azure SQL is a completely different application than MS SQL Server.

    Please post your message in the Azure SQL forum:

    https://social.msdn.microsoft.com/forums/azure/en-us/home?forum=ssdsgetstarted%2CAzureSQLDataWarehouse&filter=alltypes&sort=lastpostdesc

    Monday, September 30, 2019 3:18 PM