locked
How to migrate recursive delete query from Oracle to SQL SERVER DB RRS feed

  • Question

  • Hi 

    I am trying to migrate my application from Oracle to SQL SERVER. As part of this migration, I need to migrate this recursive query to SQL SERVER:

        DELETE FROM TableName WHERE FILE_ID IN
                 (SELECT DISTINCT FILE_ID FROM 
                     (SELECT col1, col2, FILE_ID, PARENT_ID
                         FROM TableName START WITH FILE_ID = ? CONNECT BY PRIOR FILE_ID = PARENT_ID))

    What is the right equivalent in SQL SERVER? I searched and found that CTE will be right approach. But I am just beginner starting to learn and hence please advise/correct as needed  the following SQL  query -

        WITH selectTreeForDeleteCTE as (
                          (SELECT DISTINCT FILE_ID FROM TABLENAME 
                          UNION ALL
                          SELECT col1, col2, FILE_ID, PARENT_ID FROM TableName t
                          INNER JOIN selectTreeForDeleteCTE s
                          ON t.FILE_ID = s.PARENT_ID)
                          DELETE FROM selectTreeForDeleteCTE;

    Thanks


    Thursday, May 7, 2020 4:14 AM

Answers

  • Some thing like this but please verify row count before replace "select * with delete"

     WITH selectTreeForDeleteCTE as (
                          (SELECT col1, col2, FILE_ID, PARENT_ID  FROM TABLENAME where FILE_ID= ?
                          UNION ALL
                          SELECT t.col1, t.col2, t.FILE_ID, t.PARENT_ID FROM TableName t
                          INNER JOIN selectTreeForDeleteCTE s
                          ON t.PARENT_ID = s.FILE_ID )
                          select * FROM TABLENAME where FILE_ID IN(SELECT DISTINCT FILE_ID FROM selectTreeForDeleteCTE);


    http://uk.linkedin.com/in/ramjaddu

    Thursday, May 7, 2020 8:55 AM

All replies

  • Some thing like this but please verify row count before replace "select * with delete"

     WITH selectTreeForDeleteCTE as (
                          (SELECT col1, col2, FILE_ID, PARENT_ID  FROM TABLENAME where FILE_ID= ?
                          UNION ALL
                          SELECT t.col1, t.col2, t.FILE_ID, t.PARENT_ID FROM TableName t
                          INNER JOIN selectTreeForDeleteCTE s
                          ON t.PARENT_ID = s.FILE_ID )
                          select * FROM TABLENAME where FILE_ID IN(SELECT DISTINCT FILE_ID FROM selectTreeForDeleteCTE);


    http://uk.linkedin.com/in/ramjaddu

    Thursday, May 7, 2020 8:55 AM
  • Thanks a lot. This worked.
    Thursday, May 7, 2020 5:49 PM
  • Hi KirshnaPartha - Can you please market as Answer?  - Thx

    http://uk.linkedin.com/in/ramjaddu

    Thursday, May 7, 2020 6:14 PM