How to replace multiple items in a column in sql server express table

  • ironryon

    Mr or Mrs. 500

    Points: 586

    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 :-):-):-)

  • Gail Shaw

    SSC Guru

    Points: 1004424

    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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Jeff Moden

    SSC Guru

    Points: 993884

    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


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems

  • ironryon

    Mr or Mrs. 500

    Points: 586

    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.

  • Gail Shaw

    SSC Guru

    Points: 1004424

    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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • ironryon

    Mr or Mrs. 500

    Points: 586

    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 -

    john.smith@hotmail.com

    steve.harris@microsoft.com

    david.jones@hotmail.com

    ....

    As the data will look once the @hotmail.com has been replaced with @gmail.com in the email column-

    john.smith@gmail.com

    steve.harris@microsoft.com

    david.jones@gmail.com

  • ironryon

    Mr or Mrs. 500

    Points: 586

    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..

  • Gail Shaw

    SSC Guru

    Points: 1004424

    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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • ironryon

    Mr or Mrs. 500

    Points: 586

    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 Express 9.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

  • Gail Shaw

    SSC Guru

    Points: 1004424

    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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • ironryon

    Mr or Mrs. 500

    Points: 586

    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?

  • Stephanie Giovannini

    SSCertifiable

    Points: 7422

    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.

  • Jeff Moden

    SSC Guru

    Points: 993884

    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')

    WHERE LIKE '%@Hotmail.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


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems

  • ironryon

    Mr or Mrs. 500

    Points: 586

    Stephanie Giovannini (4/21/2016)


    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.

    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.

  • ironryon

    Mr or Mrs. 500

    Points: 586

    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')

    WHERE LIKE '%@Hotmail.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 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply