Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2005
    Posts
    2

    Unanswered: Possible to use substring in an update query?

    I am attempting to take a field and break it into two seperate fields.
    Using the select statement below I am able to see it broken into two fields

    select name, substring(name, 1, instr(name, ',') - 1) first, substring(name, instr(name, ',') + 1) from doctor

    OUTPUT
    NAME | FIRST | LAST
    Doe, John | John | Doe

    What I am unsure about is how to turn this into an update query to place into the appropriate fields name 'first' and 'last'

    Thanks in advance.

  2. #2
    Join Date
    Oct 2004
    Posts
    6
    update test a set first_name = (select substring(col_with_data,...)
    from test b where <join a and b on key columns, columns that uniquely identify a row>)

    and you can have the same query for last_name.

    I am assuming you have blank columns already in the table.

    I am not an expert, but hope this helps...

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    does the table actually have 3 fields for fullname, firstname, lastname?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •