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 with 2 group by columns

Author  Topic 

cfm
Starting Member

6 Posts

Posted - 2013-01-28 : 09:49:09
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

52326 Posts

Posted - 2013-01-28 : 09:58:39
[code]
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
[/code]

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

Go to Top of Page

cfm
Starting Member

6 Posts

Posted - 2013-01-28 : 10:54:46
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
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-01-28 : 11:11:47
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 - 2013-01-28 : 11:21:39
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
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-01-28 : 11:27:13
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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-28 : 11:27:53
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 - 2013-01-28 : 11:52:08
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 - 2013-01-28 : 12:07:54
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
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-01-28 : 12:30:05
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
   

- Advertisement -