Results 1 to 4 of 4

Thread: SQL Problem

  1. #1
    Join Date
    Dec 2001
    Posts
    80

    Unanswered: SQL Problem

    Suppose I have 2 table, with the following data.

    Table TA
    K1 F1 F2
    --- --- ---
    1 4 5
    2 6 9
    3 2 3


    Table TB
    K1 F1 F2
    --- --- ---
    1 2 6
    2 7 2
    3 1 9

    I would like to do a update so that the value of F1 of table TB will be added to the F1 of table TA with the same Key value of K1, therefore, as a result of that, the content of Table TA will be like that

    Table TA
    K1 F1 F2
    --- --- ---
    1 6 5
    2 13 9
    3 3 3

    Can update statement do that?

  2. #2
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    Code:
    rbaraer@Ora10g> drop table TA;
    
    Table dropped.
    
    rbaraer@Ora10g> drop table TB;
    
    Table dropped.
    
    rbaraer@Ora10g> create table TA(K1 number primary key, F1 number, F2 number);
    
    Table created.
    
    rbaraer@Ora10g> create table TB(K1 number primary key, F1 number, F2 number);
    
    Table created.
    
    rbaraer@Ora10g> insert into TA(K1,F1,F2) values(1,4,5);
    
    1 row created.
    
    rbaraer@Ora10g> insert into TA(K1,F1,F2) values(2,6,9);
    
    1 row created.
    
    rbaraer@Ora10g> insert into TA(K1,F1,F2) values(3,2,3);
    
    1 row created.
    
    rbaraer@Ora10g> insert into TB(K1,F1,F2) values(1,2,6);
    
    1 row created.
    
    rbaraer@Ora10g> insert into TB(K1,F1,F2) values(2,7,2
      2  );
    
    1 row created.
    
    rbaraer@Ora10g> insert into TB(K1,F1,F2) values(3,1,9);
    
    1 row created.
    
    rbaraer@Ora10g>  select * from TA;
    
            K1         F1         F2
    ---------- ---------- ----------
             1          4          5
             2          6          9
             3          2          3
    
    rbaraer@Ora10g> select * from TB;
    
            K1         F1         F2
    ---------- ---------- ----------
             1          2          6
             2          7          2
             3          1          9
    
    rbaraer@Ora10g> update TA set F1 = F1 + (select F1 from TB where TB.K1=TA.K1);
    
    3 rows updated.
    
    rbaraer@Ora10g> select * from TA;
    
            K1         F1         F2
    ---------- ---------- ----------
             1          6          5
             2         13          9
             3          3          3
    
    rbaraer@Ora10g>
    HTH & Regards,

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

  3. #3
    Join Date
    Dec 2001
    Posts
    80
    thx RBARAER, you are very helpful

  4. #4
    Join Date
    Nov 2005
    Posts
    4
    You would probably need to have F1 as primary key in Table TA and have F1 as foreign key in Table TB.I hope this answers your question.

Posting Permissions

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