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.
Author |
Topic |
satheesh
Posting Yak Master
152 Posts |
Posted - 2013-07-23 : 11:10:22
|
Hi,I have field 'email' has many email address including duplicatesI can find duplicate and no of occurrence usingSELECT EmailAddress , count(*)FROM renewalGROUP BY EmailAddressHAVING COUNT(EmailAddress + Lists ) > 1 However i need to update another column 'emailcount' for duplicate id like belowemail emailcountsg00sgt@gmail.com 1sg00sgt@gmail.com 2trs675t@yahoo.com 1trs675t@yahoo.com 2sg00sgt@gmail.com 3How to modify the query to update the field.Any help will be highly appreciatedThanksRegards,SG |
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-07-23 : 11:30:42
|
[code];WITH cte AS( SELECT emailCount, ROW_NUMBER() OVER (PARTITION BY email ORDER BY email) AS NewemailCount FROM renewal)UPDATE cte SET emailCount = NewemailCount;[/code] |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2013-07-28 : 11:53:04
|
You may want to just use it in SELECT query so that you do not need to update the table whenever new email is addedMadhivananFailing to plan is Planning to fail |
|
|
|
|
|