Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2004
    Posts
    4

    Unanswered: please help with this query

    I have three tables whose structure remembers one shown below.
    I have put in some sample data. I real world these tables have more than 80,000 records each.

    My objective is to update the data in vin_res table from the data present in vin_rep table.
    My knowledge of sql ends with simple statements. this is kinda complex to me.
    so please help me out.

    ************************************************** *****************************************
    The tables are linked like this

    vin_req.ln_num = vin_res.c_id
    vin_req.r_id = vin_rep.r_id

    to get the corresponding country and state from vin_rep you have to get the most recent r_id from vin_req table.

    create table vin_req (
    ln_num varchar(15),
    r_id number
    );

    create table vin_rep
    (
    r_id number,
    ctry varchar(3),
    state varchar(2)
    );

    create table vin_res
    (
    c_id number,
    ctry varchar(3),
    state varchar(2)
    );

    insert into vin_req values ('1','1');
    insert into vin_req values ('1','2');
    insert into vin_req values ('1','3');
    insert into vin_req values ('2','4');
    insert into vin_req values ('2','5');
    insert into vin_req values ('3','6');
    insert into vin_req values ('3','7');
    insert into vin_req values ('4','8');
    insert into vin_req values ('5','9');

    insert into vin_rep values ('1','usa','de');
    insert into vin_rep values ('2','usa','pe');
    insert into vin_rep values ('3','usa','nj');
    insert into vin_rep values ('4','usa','va');
    insert into vin_rep values ('5','usa','md');

    insert into vin_res values ('1','i','e');
    insert into vin_res values ('1','i','e');
    insert into vin_res values ('1','i','e');
    insert into vin_res values ('2','i','e');
    insert into vin_res values ('3','i','j');
    insert into vin_res values ('4','i','a');
    insert into vin_res values ('5','i','d');
    Last edited by bvvinod; 02-12-04 at 17:40.

  2. #2
    Join Date
    Jul 2003
    Posts
    2,296
    you cannot do it.
    why?
    because vin_res.c_id represents more than one state.
    How are you supposed to know which state you want to insert?

    here is your join:
    PHP Code:
      1  select r.*, vin_req.*, vin_rep.*
      
    2    from vin_rep vin_reqvin_res r
      3    where
      4    vin_req
    .ln_num r.c_id and
      
    5*   vin_req.r_id vin_rep.r_id
    16
    :01:51 platform@kod1> /

          
    C_ID CTR ST LN_NUM                R_ID       R_ID CTR ST
    ---------- --- -- --------------- ---------- ---------- --- --
             
    1 i   e  1                        1          1 usa de
             1 i   e  1                        1          1 usa de
             1 i   e  1                        1          1 usa de
             1 i   e  1                        2          2 usa pe
             1 i   e  1                        2          2 usa pe
             1 i   e  1                        2          2 usa pe
             1 i   e  1                        3          3 usa nj
             1 i   e  1                        3          3 usa nj
             1 i   e  1                        3          3 usa nj
             2 i   e  2                        4          4 usa va
             2 i   e  2                        5          5 usa md 
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  3. #3
    Join Date
    Feb 2004
    Posts
    4
    i am sorry i forgot to say that you have to pick the max(r_id) from vin_req.

    so my desired output when i say "select * from vin_res" should be

    1 - usa - nj
    1 - usa - nj
    1 - usa - nj
    2 - usa - md
    3 - i - j
    4 - i - a
    5 - i - d


    I am able to accomplish this using cursors. But my problem is that in real database these tables are over 80,000 records each and when i run my program it hangs and finally i have to ctrl + alt + del.

    as i told u all that i m beginner to sql world please look at my program. it would be great if you can suggest me a better way to do it.

    thanks

    ************************************************** *******

    DECLARE

    v_lnm vin_req.ln_num%TYPE;
    v_rid vin_req.r_id%TYPE;
    v_ctry vin_rep.ctry%TYPE;
    v_state vin_rep.state%TYPE;

    CURSOR c_cursor IS

    select distinct req1.ln_num, rep1.r_id,ctry, state from vin_req req1, vin_rep rep1 where
    rep1.r_id = (
    select max(r_id) from vin_req req2 where
    req1.ln_num = req2.ln_num
    );

    BEGIN

    OPEN c_cursor;

    LOOP


    FETCH c_cursor INTO v_lnm, v_rid, v_ctry, v_state;

    EXIT WHEN c_cursor%NOTFOUND;

    update vin_res res
    set ctry = v_ctry,
    state = v_state
    where
    to_char(res.c_id) = v_lnm;

    END LOOP;

    CLOSE c_cursor;

    END;

    ************************************************** *******
    Last edited by bvvinod; 02-12-04 at 17:39.

  4. #4
    Join Date
    Dec 2003
    Location
    Noida, India.
    Posts
    171
    Try this..

    Code:
    update vin_res
    set ctry=nvl((select ctry from vin_rep where r_id=(select max(r_id) from vin_req where ln_num=vin_res.c_id)),ctry),
        state=nvl((select state from vin_rep where r_id=(select max(r_id) from vin_req where ln_num=vin_res.c_id)),state)
    Oracle can do wonders !

  5. #5
    Join Date
    Feb 2004
    Posts
    4
    Thanks for your help. The query you gave me will work but i have tables with 80,000 records so this query might take a while to execute.

    I have found another way (one of my friends helped me) of doing it and its simple. please find code below.

    UPDATE vin_res res
    SET
    (res.ctry,res.state) = (
    SELECT res.ctry,rep.state
    FROM vin_rep rep
    WHERE rep.r_id IN (
    SELECT MAX(req.r_id)
    FROM vin_req req
    WHERE req.ln_num= to_char(res.c_id)));

Posting Permissions

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