SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 deleting with 2 group by columns
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

cfm
Starting Member

6 Posts

Posted - 01/28/2013 :  09:49:09  Show Profile  Reply with Quote
I would like to delete the records but I get an error when I try add more an one group by field.

Here is the select statement of records I would like to delete.

Select AccountNumber
,Salutation
,count(accontnumber)
From AccountSalutions
where UseASPrimary = '1'
group by accountnumber, salutation
having count(accountnumber) > 1


When I use that select statement in a delete I get an error. I not sure how to use the delete statement with two group by fields

Delete FROM accountSalutations
Where accountnumber in (select accountnumber, salutation from accountsalutations
where UseAsPrimary = '1'
group by accountnumber, salutation
having count(accountnumber) > 1

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 01/28/2013 :  09:58:39  Show Profile  Reply with Quote

Delete [as] FROM accountSalutations [as]
join (select accountnumber, salutation from accountsalutations
where UseAsPrimary = '1'
group by accountnumber, salutation
having count(accountnumber) > 1)t
on t.accountnumber = [as].accountnumber
and t.salutation = [as].salutation 


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/


Edited by - visakh16 on 01/28/2013 10:02:11
Go to Top of Page

cfm
Starting Member

6 Posts

Posted - 01/28/2013 :  10:54:46  Show Profile  Reply with Quote
This delete statement did not work correctly. The delete query return 389 rows and the select statement had only 194 records.

quote:
Originally posted by visakh16


Delete [as] FROM accountSalutations [as]
join (select accountnumber, salutation from accountsalutations
where UseAsPrimary = '1'
group by accountnumber, salutation
having count(accountnumber) > 1)t
on t.accountnumber = [as].accountnumber
and t.salutation = [as].salutation 


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/



Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3760 Posts

Posted - 01/28/2013 :  11:11:47  Show Profile  Reply with Quote
That you will get fewer rows in the select than in the delete statement is to be expected. For example, if you had two rows that have the same accountnumber and salutation, the query would return only one row, but the delete would delete the two rows. Is that not what you want to accomplish? Or are you trying to remove duplicates - in the example I cited, do you want to remove both rows, or just one of the two rows so you end up with a single row for a given accountnumber and salutation combination?
Go to Top of Page

cfm
Starting Member

6 Posts

Posted - 01/28/2013 :  11:21:39  Show Profile  Reply with Quote
I just wanted to remove on of the duplicate records, not both of them.

quote:
Originally posted by James K

That you will get fewer rows in the select than in the delete statement is to be expected. For example, if you had two rows that have the same accountnumber and salutation, the query would return only one row, but the delete would delete the two rows. Is that not what you want to accomplish? Or are you trying to remove duplicates - in the example I cited, do you want to remove both rows, or just one of the two rows so you end up with a single row for a given accountnumber and salutation combination?

Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3760 Posts

Posted - 01/28/2013 :  11:27:13  Show Profile  Reply with Quote
I usually use the row_number function to do this - for example like shown below:
DELETE FROM 
(
	SELECT
		ROW_NUMBER() OVER (PARTITION BY accountnumber,salutation ORDER BY accountnumber) AS NN,*
	FROM
		accountsalutations
	WHERE
		UseAsPrimary = '1'
) s
WHERE NN > 1
Add any other column you want to the select list, change the DELETE FROM to SELECT * FROM and you can see the rows that are going to be deleted before you pull the trigger to delete.
SELECT *
(
	SELECT
		ROW_NUMBER() OVER (PARTITION BY accountnumber,salutation ORDER BY accountnumber) AS NN,
		*
	FROM
		accountsalutations
	WHERE
		UseAsPrimary = '1'
) s
WHERE NN > 1

Edited by - James K on 01/28/2013 11:33:00
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 01/28/2013 :  11:27:53  Show Profile  Reply with Quote
then you need to tell us rules based on which you want to select the records for deletion
give some sample data and explain which ones you need to delete

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

cfm
Starting Member

6 Posts

Posted - 01/28/2013 :  11:52:08  Show Profile  Reply with Quote
It does not matter what record gets deleted.


RecordId AccountNumber SalutationType UseAsPrimary Salutation
382457 42184203 INFORMAL 1 Mary
7352837 42184203 INFORMAL 1 Mary
384631 42330782 INFORMAL 1 April
2767828 42330782 INFORMAL 1 April
903373 214141028 FORMAL 1 Mrs. Hunt
2495654 214141028 FORMAL 1 Mrs. Hunt


quote:
Originally posted by visakh16

then you need to tell us rules based on which you want to select the records for deletion
give some sample data and explain which ones you need to delete

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/



Go to Top of Page

cfm
Starting Member

6 Posts

Posted - 01/28/2013 :  12:07:54  Show Profile  Reply with Quote
when I try to run the select statement I get an error: "Msg 102, Level 15, State 1, Line 9
Incorrect syntax near 's'."

quote:
Originally posted by James K

I usually use the row_number function to do this - for example like shown below:
DELETE FROM 
(
	SELECT
		ROW_NUMBER() OVER (PARTITION BY accountnumber,salutation ORDER BY accountnumber) AS NN,*
	FROM
		accountsalutations
	WHERE
		UseAsPrimary = '1'
) s
WHERE NN > 1
Add any other column you want to the select list, change the DELETE FROM to SELECT * FROM and you can see the rows that are going to be deleted before you pull the trigger to delete.
SELECT *
(
	SELECT
		ROW_NUMBER() OVER (PARTITION BY accountnumber,salutation ORDER BY accountnumber) AS NN,
		*
	FROM
		accountsalutations
	WHERE
		UseAsPrimary = '1'
) s
WHERE NN > 1


Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3760 Posts

Posted - 01/28/2013 :  12:30:05  Show Profile  Reply with Quote
Sorry about that cfm, I missed an alias:
DELETE s FROM 
(
	SELECT
		ROW_NUMBER() OVER (PARTITION BY accountnumber,salutation ORDER BY accountnumber) AS NN
	FROM
		accountsalutations
	WHERE
		UseAsPrimary = '1'
) s
WHERE NN > 1
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.1 seconds. Powered By: Snitz Forums 2000