Update using duplicate - SQL Server Forums

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

Update using duplicate

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 duplicates

I can find duplicate and no of occurrence using

SELECT EmailAddress , count(*)
FROM renewal
GROUP BY EmailAddress
HAVING COUNT(EmailAddress + Lists ) > 1

However i need to update another column 'emailcount' for duplicate id like below

email emailcount
sg00sgt@gmail.com 1
sg00sgt@gmail.com 2
trs675t@yahoo.com 1
trs675t@yahoo.com 2
sg00sgt@gmail.com 3

How to modify the query to update the field.

Any help will be highly appreciated

Thanks

Regards,
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 added

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources