April 21, 2016 at 4:08 am
Hi 🙂
Can anyone help me with a job I have to do -
I have an sql server express db where 1 table lists certain users data - in particular one of the columns in that table is called email and within that column are users email addresses.
There are 300 rows/email addresses in that column that I need to change to a different email address.
Any ideas how I do that?
Much appreciated :-):-):-)
April 21, 2016 at 4:09 am
What's the definition of the table and where is the list of new values?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 21, 2016 at 6:52 am
ironryon (4/21/2016)
Hi 🙂Can anyone help me with a job I have to do -
I have an sql server express db where 1 table lists certain users data - in particular one of the columns in that table is called email and within that column are users email addresses.
There are 300 rows/email addresses in that column that I need to change to a different email address.
Any ideas how I do that?
Much appreciated :-):-):-)
If all the email addresses need to be changed the same way and all have something in common, use a LIKE to find them and REPLACE to update them.
Without knowing more about the particular data, that's the best I can do for a suggestion.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 21, 2016 at 7:00 am
GilaMonster (4/21/2016)
What's the definition of the table and where is the list of new values?
Hi GilaMonster
Thanks for taking the time to help.
Im an SQL newbie so I dont know what you mean by table definition...(typical that right now due to an outage I cant login to our server to try and find out too!)
The list of new values are in an excel spreadsheet.
April 21, 2016 at 7:10 am
Oh, so what, in the spreadsheet tells you what row in the table the new email address belongs to?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 21, 2016 at 7:11 am
Jeff Moden (4/21/2016)
ironryon (4/21/2016)
Hi 🙂Can anyone help me with a job I have to do -
I have an sql server express db where 1 table lists certain users data - in particular one of the columns in that table is called email and within that column are users email addresses.
There are 300 rows/email addresses in that column that I need to change to a different email address.
Any ideas how I do that?
Much appreciated :-):-):-)
If all the email addresses need to be changed the same way and all have something in common, use a LIKE to find them and REPLACE to update them.
Without knowing more about the particular data, that's the best I can do for a suggestion.
Thanks Jeff
Does this help, all email address with @hotmail.com must be replaced with the new @gmail.com email address.
The current data in the email column -
....
As the data will look once the @hotmail.com has been replaced with @gmail.com in the email column-
April 21, 2016 at 7:31 am
GilaMonster (4/21/2016)
Oh, so what, in the spreadsheet tells you what row in the table the new email address belongs to?
Hi GilaMonster
No it wont tell me what row. What the spreadsheet will have is the username and the new email address....
So it would be like this
username email address
J1234 John.Smith@gmail.com
D1234 David.Jones@gmail.com
.....
In the table where I need to update the email address column there is also a username column..
April 21, 2016 at 7:34 am
So it will tell you what row, the row in the table with the matching username to the row in the spreadsheet.
In that case, import the spreadsheet into a database table (from Management Studio, right click the DB in object explorer, tasks > import data)
Then you can put together an UPDATE statement that will do the change.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 21, 2016 at 7:48 am
GilaMonster (4/21/2016)
So it will tell you what row, the row in the table with the matching username to the row in the spreadsheet.In that case, import the spreadsheet into a database table (from Management Studio, right click the DB in object explorer, tasks > import data)
Then you can put together an UPDATE statement that will do the change.
Okidoki - Thanks, might need some help with that UPDATE statement 😉
I'm using Microsoft SQL Server Management Studio Express9.00.1399.00 - and when i right click on the DB in object explorer as you mention I dont have the import option.
What I do have is -
Detach
Shrink
Backup
Restore
Generate Scripts
April 21, 2016 at 7:53 am
Right-click -> tasks -> Import Data. It's below 'generate scripts'
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 21, 2016 at 8:12 am
Definitely not there on my version...
Last Task option is Generate Scripts.
Maybe something the DB admins have restricted or just different on this Express version?
April 21, 2016 at 8:44 am
What version of SSMS (SQL Server Management Studio) are you running? (Help/About)
Do you have SQL Server Express with Advanced Services? If you downloaded it, you might know that.
April 21, 2016 at 5:54 pm
ironryon (4/21/2016)
GilaMonster (4/21/2016)
Oh, so what, in the spreadsheet tells you what row in the table the new email address belongs to?Hi GilaMonster
No it wont tell me what row. What the spreadsheet will have is the username and the new email address....
So it would be like this
username email address
J1234 John.Smith@gmail.com
D1234 David.Jones@gmail.com
.....
In the table where I need to update the email address column there is also a username column..
According to your original post, you already have the data in the table, so we don't actually need to worry about the spreadsheet. Just what's in the table.
I don't know what your table name is or the datatypes but, if all you want to do is change every occurrence of "@Hotmail.com" to "@GMail.com", the substitute the correct schema name and table name into the following code and it should do fine. You included a space in the "email address" column, which is why it's included in brackets. You may have to change that as well.
Once you made the necessary changes to the following code, run it.
BEGIN TRANSACTION
UPDATE dbo.yourtablenamehere
SET = REPLACE(,'@Hotmail.com','@GMail.com')
;
-- ROLLBACK -- COMMIT
IF the returned rowcount is what you expected, then double-click on COMMIT to select it and run just the COMMIT code to make it permanent.
If the returned rowcount is NOT what you expected, then double-click on ROLLBACK to select it and run just the ROLLBACK code to undo the update and then figure out what went wrong.
As a bit of a sidebar, some people like to run their UPDATE code as a SELECT first as a bit of a test. I usually do the same but, either way, I always use the BEGIN TRANSACTION and the commented out ROLLBACK and COMMIT to help keep accidents from happening. Never walk away from an active transaction because you could end up blocking the database up pretty good.
My next suggestion, since you're a newbie, would be to spend some time studying SQL Server and T-SQL (the language).
--Jeff Moden
Change is inevitable... Change for the better is not.
April 22, 2016 at 8:20 am
Stephanie Giovannini (4/21/2016)
Do you have SQL Server Express with Advanced Services? If you downloaded it, you might know that.
Hi Stephanie
I have Microsoft SQL Server Management Studio Express. I didnt install it so Im not sure if its the advanced setup or not.
April 22, 2016 at 8:25 am
Jeff Moden (4/21/2016)
ironryon (4/21/2016)
GilaMonster (4/21/2016)
Oh, so what, in the spreadsheet tells you what row in the table the new email address belongs to?Hi GilaMonster
No it wont tell me what row. What the spreadsheet will have is the username and the new email address....
So it would be like this
username email address
J1234 John.Smith@gmail.com
D1234 David.Jones@gmail.com
.....
In the table where I need to update the email address column there is also a username column..
According to your original post, you already have the data in the table, so we don't actually need to worry about the spreadsheet. Just what's in the table.
I don't know what your table name is or the datatypes but, if all you want to do is change every occurrence of "@Hotmail.com" to "@GMail.com", the substitute the correct schema name and table name into the following code and it should do fine. You included a space in the "email address" column, which is why it's included in brackets. You may have to change that as well.
Once you made the necessary changes to the following code, run it.
BEGIN TRANSACTION
UPDATE dbo.yourtablenamehere
SET = REPLACE(,'@Hotmail.com','@GMail.com')
;
-- ROLLBACK -- COMMIT
IF the returned rowcount is what you expected, then double-click on COMMIT to select it and run just the COMMIT code to make it permanent.
If the returned rowcount is NOT what you expected, then double-click on ROLLBACK to select it and run just the ROLLBACK code to undo the update and then figure out what went wrong.
As a bit of a sidebar, some people like to run their UPDATE code as a SELECT first as a bit of a test. I usually do the same but, either way, I always use the BEGIN TRANSACTION and the commented out ROLLBACK and COMMIT to help keep accidents from happening. Never walk away from an active transaction because you could end up blocking the database up pretty good.
My next suggestion, since you're a newbie, would be to spend some time studying SQL Server and T-SQL (the language).
Hi Jeff
Not all the users in the table will have their email addresses updated to the example @gmail.com.
There are approx 7000 users/rows in this table with a variety of email address values.
So only 300 of the 7000 will need to be updated with a new email address. Two of the columns in the table are for "username" and "email address".
The spreadsheet I have has 300 usernames and email addresses.
Thanks for your suggestion - I definitely need to study a lot 🙂
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply