StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>How often are you going to be running this that it matters whether you use temp tables or not? Maybe you should consider adding constraints to the table so you only have to do this once...</p> <p>That said, in all honesty, the best way to do this for SQL Server 2000 is probably to use the #temp table as you're already doing. If you were trying to delete all but one of each dupe, then you could do something like:</p> <ul> <li>insert the distinct rows into a separate table</li> <li>delete all the rows from the old table</li> <li>move the distinct rows back into the original table</li> </ul> <p>I've also done things like copy the distinct rows into a new table, drop the old table, and rename the new table.</p> <p>But this doesn't sound like the goal. Can you show the code you're currently using with the #temp table? I'm trying to envision how you're identifying the rows to keep, and maybe seeing your existing code will trigger something.</p> <p><strong>EDIT</strong> - now with better understood requirements, I can propose the following query. Please test it on a <strong>copy</strong> of the table first!</p> <pre><code>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; </code></pre> <p>Note that this doesn't confirm that there are exactly one or two rows that match the grouping on columnA and columnB. Also note that if you run this twice it will delete the remaining row that still matches the subquery!</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload