Results 1 to 6 of 6

Thread: Self join

  1. #1
    Join Date
    Nov 2009
    Posts
    8

    Unanswered: Self join

    Hi,

    I need to parse the part of name and I need to add it into a new col in a table. May I know how I can do that. Do I need to use a self join.

    EMPLOYEE TABLE

    HTML Code:
    NAME               EID          FNAME
    fname_lname      101
    I was trying the following query

    insert into EMPLOYEE(FNAME)
    select substr(NAME,0,INSTR(NAME,'_')-1) as fname
    from EMPLOYEE e1, EMPLOYEE e2
    where e1.eid = e2.eid.

    but I am getting this error, ORA-00918: column ambiguously defined

    May I know what is the right sql query to achive this?

    Thanks,
    shijumic

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >May I know what is the right sql query to achive this?
    to which row (e1 or e2) does NAME originate?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Nov 2009
    Posts
    8
    Every row has a unique eid and every row has name.

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >Every row has a unique eid and every row has name.
    Every day the Sun rises in the East.

    While both statement are true, neither has anything to do with the error you reported.

    >select substr(NAME,0,INSTR(NAME,'_')-1) as fname
    >from EMPLOYEE e1, EMPLOYEE e2
    The error occurs because Oracle is confused whether "NAME"
    comes from EMPLOYEE e1 or comes from EMPLOYEE e2
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Not really sure what you are trying to do, but

    As out friend points out you need to get the name from one of the named instances

    insert into EMPLOYEE(FNAME)
    select substr(NAME,0,INSTR(e1.NAME,'_')-1) as fname
    from EMPLOYEE e1, EMPLOYEE e2
    where e1.eid = e2.eid.

    OR

    insert into EMPLOYEE(FNAME)
    select substr(NAME,0,INSTR(e2NAME,'_')-1) as fname
    from EMPLOYEE e1, EMPLOYEE e2
    where e1.eid = e2.eid.


    In any case, most of that doesn't make sense. Why not just do

    insert into EMPLOYEE(FNAME)
    select substr(NAME,0,INSTR(NAME,'_')-1) as fname
    from EMPLOYEE


    ???


    What's the difference?

    SUBSTR takes 0 as valid parameter?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    You seem to have a misconception, if you added a new column into your table and are trying to change it to the first name of the employee you can't do an insert, you must do an UPDATE.

    Code:
    update employee a
    set a.fname = substr(a.NAME,1,INSTR(a.NAME,'_')-1)
    where INSTR(a.NAME,'_') > 0;
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Posting Permissions

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