Results 1 to 9 of 9
  1. #1
    Join Date
    Nov 2007
    Posts
    7

    Unanswered: adding coulmn according to other table

    i have two tables for ex. table A and table B
    X Y Z are Composite Key.

    table A like:
    X_____Y_____Z_______H _______L _______F
    1_____22_____35_____ 'no'________'m'
    2_____60_____67______'yes'_______'f'
    7_____89_____90______'yes'_______'m'
    :______:_______:_______:_________:

    table B like:
    X_____Y____Z_____F
    1_____22____35_____44
    2_____60____67_____98
    7_____89____90_____65
    :______:_____:______:

    i want to add F coulum on table A according to X Y Z
    to be like this
    X____Y_____Z_____H_____L_____F
    1_____22_____35____'no'____'m'____44
    2_____60_____67____'yes'___'f' ____98
    7_____89_____90____'yes'___'m'____65
    :______:______:______:____:______:
    and thx u.
    Last edited by abood85; 11-28-07 at 23:05.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Please realize that nobody here will do your homework assignment for you.

    The "requirements" are wrong on a couple of different levels.
    1) The tables are not in Third Normal Form
    2) It is a Bad Idea to be duplicating data between two tables.
    3) Data should reside in 1 table & 1 table only.

    One "solution" that does not duplicate data is CREATE VIEW......
    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 2007
    Posts
    7
    thank u for ur quick reply, but i want to delete table B and at the same time i dont want to lose column F. please help me.

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    start by write a SELECT statement that joins TABLE_A & TABLE_B & properly presents all the columns you wish to retain.
    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 2007
    Posts
    7
    can anyone give me the solution, please. coz it makes big issues in my system.

  6. #6
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    I don't see any issues.
    Why can't you produce the initial SELECT criteria?
    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.

  7. #7
    Join Date
    May 2005
    Location
    Mumbai, India
    Posts
    2
    Quote Originally Posted by abood85
    thank u for ur quick reply, but i want to delete table B and at the same time i dont want to lose column F. please help me.
    Try this.

    Alter table A add column F datatype;

    update table A a1 set F = (select F from B b1 where a1.x=b1.x and a1.y=b1.y and a1.z=b1.z);

    drop table A;

    Hope this helps.


    -----------------------
    Regards,
    Archana P. Pandey
    "While one person hesitates because he feels inferior, the other is busy making mistakes and becoming superior."

    http://justoracle.blogspot.com/

  8. #8
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    It was nice of you to help, but this is such a simple problem that anyone with oracle experence could do it. That is why anacedent wanted the user to show what they had already tried. We will help people with their homework, but only after they make an effort them selves.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  9. #9
    Join Date
    Nov 2007
    Posts
    7
    thank you. and nice to take good advice from u. thx again.

Posting Permissions

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