Deleting records where several fields are equal? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Deleting records where several fields are equal?

Author

Topic

Rasta Pickles
Posting Yak Master

174 Posts

Posted - 2012-08-10 : 13:57:42

Several columns but not all......is there a way?

Someone at work said they believed it could be done with a CTE but couldn't provide more info so I thought I'd ask the experts

Assume I have a table called test with records


header       F1 F2 F3 F4 F5 F6
20/04/12      1  1  1  2  1  1
20/04/12      1  1  1  1  1  1
21/04/12      1  1  1  1  1  1

how do I delete the first record (the one where F4 = 2)?

TIA.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-08-10 : 14:11:32

The following must be what they were referring to. First run this query to make sure you are indeed getting the records you want to delete:

;WITH cte AS
(
	SELECT *, ROW_NUMBER() OVER (PARTITION BY header,F1,F2,F3,F5,F6 ORDER BY F4) AS RN
	FROM YourTable
) SELECT * FROM cte WHERE RN > 1;

If youare satisfied, run this to delete:

;WITH cte AS
(
	SELECT ROW_NUMBER() OVER (PARTITION BY header,F1,F2,F3,F5,F6 ORDER BY F4) AS RN
	FROM YourTable
) DELETE FROM cte WHERE RN > 1;

Rasta Pickles
Posting Yak Master

174 Posts

Posted - 2012-08-10 : 15:38:29

Many thanks, I will try it on Monday.

Just as a learning aid, you're excluding F4 in the SELECT but also ordering results by it?

Can you explain how this works because it seems a very powerful tiny piece of code?

TIA

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-08-10 : 15:44:02

It is not that we are including F4 in the order by clause because it is excluded from the partition by clause or vice versa.

The columns in the partition by clause should include everything that you want to consider as one group. So in this case even if you partitioned only by header column, the result would be the same. The order by clause is the ordering scheme you want to use within each group.

I am not explaining it very well :) Take a look at this page and you will see what I mean: http://www.simple-talk.com/sql/learn-sql-server/working-with-window-functions-in-sql-server/

SQL 2012 has much more expanded windowing functions.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources