Results 1 to 11 of 11

Thread: SQL Query

  1. #1
    Join Date
    Jan 2006
    Posts
    74

    Unanswered: SQL Query

    Afternoon

    I need a little help with some sql. I have a table within my oracle db with the following columns


    Name
    Firstname
    surname

    The name column is not being populated to read the surname and then the Firstname ie it's reading Paul Smith instead of Smith Paul. I need an update sql that will populate the Name Column so the surname is in first followed by a , then the firstname.

    A point in the right direction would be great.

    Thanks in advance.

  2. #2
    Join Date
    Dec 2007
    Posts
    253
    I would question the wisdom of storing the name column at all. I would be inclined to suggest calculating this value when needed.

  3. #3
    Join Date
    Jan 2006
    Posts
    74
    We have integrated a few of our systems and this is required.

  4. #4
    Join Date
    Dec 2007
    Posts
    253
    Then it is a simple
    surname||', '||firstname

  5. #5
    Join Date
    Jan 2006
    Posts
    74
    sorry to be a pain but could you send me the full query my sql is not great.

    Thanks

  6. #6
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Check this example and, if necessary, change it to suit your needs.
    Code:
    SQL> WITH TEST AS
      2    (SELECT 'Paul Smith' name FROM dual
      3     UNION
      4     SELECT 'Little Foot' FROM dual
      5    )
      6    SELECT SUBSTR(name, 1, INSTR(name, ' ')) first_name,
      7           SUBSTR(name, INSTR(name, ' ') + 1, LENGTH(name)) surname
      8    FROM TEST;
    
    FIRST_NAME  SURNAME
    ----------- -----------
    Little      Foot
    Paul        Smith
    
    SQL>

  7. #7
    Join Date
    Dec 2007
    Posts
    253
    Sorry but no. Have a look at here
    It will give you the syntax that you require.
    Obviously that was a reply to the OP, not you littlefoot (although I think you may have misunderstood the OP's question)

  8. #8
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    I am not trying to be mean, but if you are not capable of writting such a simple update statement and you do not have anyone else that you can ask at your place of employment, could you tell us what your company is so that I can drop any stock that I might have in it! That being said,

    update my_table
    set name = surname||', '||firstname;
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  9. #9
    Join Date
    Jan 2006
    Posts
    74
    I actually work for a Charity who are trying to find a cure for cancer!

  10. #10
    Join Date
    Jan 2006
    Posts
    74
    What I also meant to say is that you def wouldnt have bought shares so you got nothing to worry about.

  11. #11
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    Quote Originally Posted by pablolee
    I would question the wisdom of storing the name column at all. I would be inclined to suggest calculating this value when needed.
    I agree.
    Quote Originally Posted by bmistry
    We have integrated a few of our systems and this is required.
    I would recommend to use a view :
    Code:
    CREATE OR REPLACE VIEW V_MY_TABLE(
        Name,
        FirstName,
        SurName
    )
    AS
    SELECT SurName||', '||FirstName,
        FirstName,
        SurName
    FROM My_Table;
    Doing so, any insert/update on my_table will only affect firstname and surname (no need to calculate NAME) and the view will always provide the three.

    HTH & Regards,

    rbaraer
    ORA-000TK : No bind variable detected... Shared Pool Alert code 5 - Nuclear query ready .

Posting Permissions

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