I wish to remove for the most part one record for every unique (columnA, columnB)-tuple during my following remove statement:

DELETE FROM tableA
WHERE columnA IN 
    (
    --some subqueryA
    )
AND columnB IN
    (
    --some subqueryB
    )

How's this accomplished? Please only consider individuals claims that actually work when used against MSS 2000 (i.e., T-SQL 2000 syntax). I'm able to get it done with iterating via a temptable but I wish to write it only using sets.

Example:

subqueryA returns 1
subqueryB returns 2,3

When the original table contained (columnA, columnB, columnC)

5,2,5
1,2,34
1,2,45
1,3,86

Then

1,2,34
1,3,86 

ought to be erased. Each unique (columnA, columnB)-tuple can look for the most part two times in tableA and every time I run my SQL statement I wish to remove for the most part one of these simple unique combinations - never two.

If there's one record for any given unique (columnA, columnB)-tuple, remove it.

If you will find two records for any given unique (columnA, columnB)-tuple, remove only one of these.

Delete tabA
from  TableA tabA 
Where tabA.columnC in (
      select  max(tabAA.columnC)  from TableA tabAA
      where tabAA.columnA in (1)
      and tabAA.columnB in (2,3)
      group by tabAA.columnA,tabAA.columnB
)

How frequently will you be running this it matters whether you utilize temp tables or otherwise? Perhaps you should attempt to add constraints towards the table which means you just do that once...

Nevertheless, in most honesty, the easiest method to do that for SQL Server 2000 is most likely to make use of the #temp table as you are already doing. Should you be attempting to remove basically among each dupe, then you may make a move like:

  • place the distinct rows right into a separate table
  • remove all of the rows in the old table
  • slowly move the distinct rows into the original table

I have also done such things as copy the distinct rows right into a new table, drop that old table, and relabel the brand new table.

But this does not seem such as the goal. Are you able to show the code you are presently using using the #temp table? I am attempting to picture how you are determining the rows to help keep, and perhaps seeing your overall code will trigger something.

EDIT - with better understood needs, I'm able to propose the next query. Please test drive it on the copy on the table first!

DELETE a 
FROM dbo.TableA AS a
INNER JOIN 
(
   SELECT columnA, columnB, columnC = MIN(columnC) 
      FROM dbo.TableA
      WHERE columnA IN
      (
        -- some subqueryA
        SELECT 1
      )
      AND columnB IN 
      (
        -- some subqueryB
        SELECT 2 UNION SELECT 3
      )
      GROUP BY columnA, columnB
) AS x
ON  a.columnA = x.columnA
AND a.columnB = x.columnB
AND a.columnC = x.columnC;

Observe that this does not make sure you will find exactly a couple of rows that match the grouping on columnA and columnB. Also observe that should you run this two times it'll remove the rest of the row that also matches the subquery!