CODEWITHSUNDEEP
sqlsql-servercommitexecutesql-delete
elarrow
elarrow

Reputation: 697

How do I commit/execute deletion row by row in SQL (MSSQL)

I have a simple table but a long one (few millions or rows). The table contains many paired rows which I need to delete. The row data is not distinct! There are single rows (which has no a paired row) The table pairs are defined by cross info in two columns concatenated to a 3rd column. I would like to have only one row of each data identifier. Therefore, I need the myTable to shrink immediately whereis a condition is met. I tried:

myIndexColumn = Column1 + Column2 + Column3
myReversedIndexColumn = Column2 + Column1 + Column3

CREATE NONCLUSTERED INDEX myIndex1 ON myDB.dbo.myTable (
  myIndexColumn ASC
)
CREATE NONCLUSTERED INDEX myIndex2 ON myDB.dbo.myTable (
  myReversedIndexColumn ASC
)

DELETE FROM myDB.dbo.myTable
WHERE myIndexColumn in (SELECT myReversedIndex FROM myDB.dbo.myTable)

The problem is that both paired data is deleted instead of leaving one row of the data.

Obviously, that is because the DELETE commits changes only after running the entire transaction.

If I could persuid the MS SQL 2008 R2 Express edition to commit the DELETE upon condition is met, the SELECT clause would have output a shorter list on each row test to delete.

How do I do that?

Upvotes: 0

Views: 1293

Answers (1)

gbn
gbn

Reputation: 432361

To not delete the cases where column1 = column2

DELETE FROM myDB.dbo.myTable
WHERE myIndexColumn in (SELECT myReversedIndex FROM myDB.dbo.myTable)
  AND column1 <> column2

To remove column1 = column2

;with cte as
(
    select *,
        row_number() over (
                partition by Column1 + Column2 + Column3
                order by (SELECT 1)
                ) rn
    from yourtable
    where column1 = column2
)
delete cte where rn > 1

The CTE can be used to delete all duplicates too

;with cte as
(
    select *,
        row_number() over (
                partition by 
                      CASE WHEN Column1 > Column2 THEN Column2 ELSE Column1 END + 
                      CASE WHEN Column1 > Column2 THEN Column1 ELSE Column2 END + 
                      Column3
                order by (SELECT 1)
                ) rn
    from yourtable
)
delete cte where rn > 1

Upvotes: 1

Related Questions