Help needed regarding the procedure

Hi All

I am new to sqlserver.I got the doubt when i want to perform a task.Need yours advice

In a table i am having a column email with mailids. most of the rows are with the value email@.email.com.
My requirement is i want to change the value email@.email.com as email1@.email.com,email2@.email.com,.....like that
with autoincrement.Here primarykey attribute name is id

Waiting for valuable suggestions

Baba

I take it you don't want to update ALL emails in your table, just the duplicate ones (email@.email.com)?

If so, it depends on whether the number is important or whether you just want to make each value distinct.

To just get distinct values you could just run:

Code Snippet

UPDATE table

SET email = 'email' + CAST(id AS VARCHAR(100)) + '@.email.com'

WHERE email = 'email@.email.com'

If the number is important and you want the values to go sequentially from 1-> n then you'll probably have to use some procedural code, such as a cursor though you'll still need an update statement similar to the one above.

HTH!

|||
Thank u for u r reply.

I applied cursors concept and i updated it.

Thank u

Baba
|||

You might want to show us what you come up with when you are done so that we can verify it for you; it is always a good idea to have another set of eyes to look at something.

|||

There's a way to get the numbers to be sequential without cursors etc:

Code Snippet

UPDATE table

SET email = 'email' + Convert(varchar(100), (select count(*) from table t2 where t2.id < t1.id and t2.email = 'email@.email.com')) + '@.email.com'

FROM table t1

WHERE email = 'email@.email.com'

It's late and I haven't tested it so there may be errors, but hopefully that gives you the general idea.

Sean