#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2013
    Posts
    3
    Rep Power
    0

    Sql update set replace all like where like


    First post.

    In a table called 'customers' I want to replace all the firstnames that begin with 'A' with 'Afirstname'; resulting in 'Adam', 'Alan', 'Anita', 'Anna', 'Anuska', etc. getting the firstname of 'Afirstname'.

    I was thinking something like:
    UPDATE customers SET firstname = REPLACE (firstname, 'A%', 'Afirstname') WHERE firstname LIKE 'A%'

    The query is happy to run without errors but it doesn't return any rows and all the 'A' names remain unchanged. Can anyone here help me figure this one out?
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,179
    Rep Power
    4279
    can you please explain why you want to destroy data like that?

    why not just do the Afirstname thing whenever you SELECT from the table?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2013
    Posts
    3
    Rep Power
    0
    Originally Posted by r937
    can you please explain why you want to destroy data like that?
    It's a table from a company database I have copied to my own laptop so I can run some tests on in, but for data protection purposes I need to render the actual user information anonymous - without destroying it so much I can't use it. Makes sense?

    Getting assistance from another source I have a solution to it. It seems I was, both making a more complicated query than necessary, and not understanding properly what REPLACE needs. The solution that works for me is this:

    UPDATE customers SET firstname = 'Afirstname' WHERE firstname LIKE 'A%'

    Thank you for looking at this and replying.
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,179
    Rep Power
    4279
    Originally Posted by Henriette
    UPDATE customers SET firstname = 'Afirstname' WHERE firstname LIKE 'A%'
    you planning on doing 26 of those? you could do them all at once, you know
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  8. #5
  9. Hockey face
    Devshed Supreme Being (6500+ posts)

    Join Date
    Nov 2001
    Location
    St. Catharines, Canada
    Posts
    8,147
    Rep Power
    1316
    You can use a simple tool like this one:
    http://www.identitygenerator.com/
    to create fake data for the table. Much easier in my opinion than what you want to do.
  10. #6
  11. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2013
    Posts
    3
    Rep Power
    0
    Originally Posted by r937
    you planning on doing 26 of those? you could do them all at once, you know
    I kinda guessed it might be possible, but I don't know exactly how. How would you do it?

    Originally Posted by Guelphdad
    You can use a simple tool like this one: <snip> to create fake data for the table. Much easier in my opinion than what you want to do.
    Thank you for that link, that's definitely a very handy little tool.
  12. #7
  13. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,179
    Rep Power
    4279
    Originally Posted by Henriette
    How would you do it?
    Code:
    UPDATE customers SET firstname = CONCAT(LEFT(firstname,1),'firstname')
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo