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
