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 AccountSalutionswhere UseASPrimary = '1'group by accountnumber, salutationhaving count(accountnumber) > 1When 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 fieldsDelete FROM accountSalutationsWhere accountnumber in (select accountnumber, salutation from accountsalutationswhere UseAsPrimary = '1'group by accountnumber, salutationhaving 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 accountsalutationswhere UseAsPrimary = '1'group by accountnumber, salutationhaving count(accountnumber) > 1)ton t.accountnumber = [as].accountnumberand t.salutation = [as].salutation [/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
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 accountsalutationswhere UseAsPrimary = '1'group by accountnumber, salutationhaving count(accountnumber) > 1)ton t.accountnumber = [as].accountnumberand t.salutation = [as].salutation ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/
|
|
|
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? |
|
|
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?
|
|
|
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') sWHERE 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') sWHERE NN > 1 |
|
|
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 deletiongive some sample data and explain which ones you need to delete------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
cfm
Starting Member
6 Posts |
Posted - 2013-01-28 : 11:52:08
|
It does not matter what record gets deleted.RecordId AccountNumber SalutationType UseAsPrimary Salutation382457 42184203 INFORMAL 1 Mary7352837 42184203 INFORMAL 1 Mary384631 42330782 INFORMAL 1 April2767828 42330782 INFORMAL 1 April903373 214141028 FORMAL 1 Mrs. Hunt2495654 214141028 FORMAL 1 Mrs. Huntquote: Originally posted by visakh16 then you need to tell us rules based on which you want to select the records for deletiongive some sample data and explain which ones you need to delete------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/
|
|
|
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 9Incorrect 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') sWHERE 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') sWHERE NN > 1
|
|
|
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') sWHERE NN > 1 |
|
|
|