| Author |
 Topic  |
|
|
cfm
Starting Member
6 Posts |
 Posted - 01/28/2013 : 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
India
47157 Posts |
Posted - 01/28/2013 : 09:58:39
|
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 |
 |
|
|
cfm
Starting Member
6 Posts |
Posted - 01/28/2013 : 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/
|
|
|
|
James K
Flowing Fount of Yak Knowledge
1515 Posts |
Posted - 01/28/2013 : 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? |
|
|
|
cfm
Starting Member
6 Posts |
Posted - 01/28/2013 : 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?
|
|
|
|
James K
Flowing Fount of Yak Knowledge
1515 Posts |
Posted - 01/28/2013 : 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 |
Edited by - James K on 01/28/2013 11:33:00 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47157 Posts |
Posted - 01/28/2013 : 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/
|
|
|
|
cfm
Starting Member
6 Posts |
Posted - 01/28/2013 : 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/
|
|
|
|
cfm
Starting Member
6 Posts |
Posted - 01/28/2013 : 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
|
|
|
|
James K
Flowing Fount of Yak Knowledge
1515 Posts |
Posted - 01/28/2013 : 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 |
|
|
| |
Topic |
|
deleting with 2 group by columns
