6 Replies Latest reply: Jan 25, 2013 1:56 PM by Frank Kulash RSS

    SQL to update two columns in TABLE2 from TABLE1

    987213
      I know this is a simple question for some of you, but I am new to SQLs, so please help...

      I have two tables TABLE1 & TABLE2 as below, both tables contains more then 50million records:
      SELECT * FROM TABLE1.
      &&&&&&&&&&&&&&&&&&&&&&&&&&&
      ID                        BUS_FID                WORKID                  STATIONID                  
      ---------------------- ---------------------- ---------------------- ---------------------- 
      28400000117234         245                    13461428.25           16520877.8             
      28400000117513         403                    13461428.25           16520877.8             
      28400000117533         423                    13461428.25           16520877.8             
      28400000117578         468                    13461428.25           16520877.8             
      28400000117582         472                    13461428.25           16520877.8             
      
      
      SELECT * FROM TABLE2.
      &&&&&&&&&&&&&&&&&&&&&&&&&&&
      BUS_FID                    ID                 TRPELID                RELPOS                 WORKID                 STATIONID                
      ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- 
      114                    28400000117658         28400000035396         23.225                                                               
      115                    28400000117659         28400000035396         23.225                                                               
      116                    28400000117660         28400000035396         23.225                                                               
      117                    28400000117661         28400000035396         23.225                                                               
      118                    28400000117662         28400000035396         23.225                                                               
      119                    28400000117663         28400000035396         23.225                                                               
      120                    28400000117664         28400000035396         23.225                                                               
      121                    28400000117665         28400000035396         23.225                                                               
      122                    28400000117666         28400000035396         23.225                                                               
      123                    28400000117667         28400000035396         23.225                                                               
      124                    28400000117668         28400000035396         23.225                                                               
      125                    28400000117669         28400000035396         23.225                                                               
      126                    28400000117670         28400000035396         23.225    
      Now I tried to use following SQL to update WORKID & STATIONID columns in TABLE2 but failed. BUS_FID in both tables have been UNIQUE indexed and they can be used as primary keys to join these two tables.
      UPDATE (
        SELECT  p.WORKID px,
                p.STATIONID py,
                p.BUS_FID pid,
                temp.WORKID tempx,
                temp.STATIONID tempy,
                temp.BUS_FID tempid
          FROM  TABLE1 temp, 
                TABLE2 p
          WHERE pid = tempid
      )
        SET px = tempx,
            py = tempy;
      
      COMMIT;
      with above code, Oracle returned following errors:
      SQL Error: ORA-00904: "TEMPID": invalid identifier
      00904. 00000 -  "%s: invalid identifier"
      Can anyone help me to correct it? Thanks~~~
      BTW, both two tables contains over 50 million records. So, if you have a better SQL to perform the same task, please let me know!

      Appreciated your help at advance.