Home » SQL & PL/SQL » SQL & PL/SQL » Update statement using 2 tables (Oracle 9i)
Update statement using 2 tables [message #426425] Thu, 15 October 2009 06:45 Go to next message
virmahi
Messages: 266
Registered: April 2008
Location: India
Senior Member
Hi,
I have to insert record to a transaction using two tables.
The first table 'parking_shalina_erp_data' has the all the transaction data to be inserted into table 'smp_po_details'.

The column 'VENDOR_LOCATION_ITEM_ID' in table 'smp_po_details' has to be inserted from table 'SMP_VENDOR_LOCATION_ITEM' where vendor_id and itemcode from table 'SMP_VENDOR_LOCATION_ITEM' and 'parking_shalina_erp_data' matches.

I have done this using a procedure with loops as not sure how to do for the column 'vendor_location_item_id' that I take from table 'SMP_VENDOR_LOCATION_ITEM'.

Can this be done by a sql query without using loops!!

I have not given the table create script as its very long.


CREATE OR REPLACE PROCEDURE shalina_data_segregation1 (p_failed_rec OUT NUMBER)
IS 
   TYPE ref_cursor IS REF CURSOR;

   cur_ref_type                    ref_cursor;
   cur_rec_parking                 parking_shalina_erp_data%ROWTYPE;
   error_message                   VARCHAR2 (250);
   v_query                         VARCHAR2 (1000);
   var_vendor_loc_item_id  NUMBER := 0;
  
 BEGIN

  p_failed_rec := 0;
    v_query := 'SELECT * FROM parking_shalina_erp_data where po_number is not null'; 
                  
         OPEN cur_ref_type FOR v_query;
          LOOP
         
             BEGIN
                 FETCH cur_ref_type INTO cur_rec_parking;
                 EXIT WHEN cur_ref_type%NOTFOUND;  
                                        
             IF cur_rec_parking.vendor_code IS NOT NULL AND  cur_rec_parking.item_code is not null then
              
              SELECT VENDOR_LOCATION_ITEM_ID INTO var_vendor_loc_item_id
                FROM SMP_VENDOR_LOCATION_ITEM
               WHERE VENDOR_ID = to_number(cur_rec_parking.vendor_code)
                 AND itemcode = cur_rec_parking.item_code; 
        
              
                 IF  var_vendor_loc_item_id  > 0  THEN
              
                    INSERT INTO smp_po_details  
                      values (cur_rec_parking.po_number,cur_rec_parking.po_date,
                             cur_rec_parking.po_quantity, cur_rec_parking.po_rate,
                             cur_rec_parking.po_status,cur_rec_parking.po_totalamount,
                             cur_rec_parking.po_pending, var_vendor_loc_item_id,
                              cur_rec_parking.po_amendement);
                            
                  END IF; 
                
              ELSE
              p_failed_rec  := p_failed_rec  + 1;                   
             end if;
              
             EXCEPTION
                 WHEN no_data_found
                 THEN
                    BEGIN
                       error_message := SQLERRM;
                         dbms_output.put_line('error_message 210: ' || SQLERRM);                       
                    END;                           
             end;
    end loop;
           
 END shalina_data_segregation1;
/


Please let me know if this can be done using a single sql query anyhow....

Thanks,
Mahi
Re: Update statement using 2 tables [message #426428 is a reply to message #426425] Thu, 15 October 2009 06:59 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
It's easy - just join the two queries together, and do an INSERT... SELECT
Ideally, you'd have a list of column names in the insert statement as well, so that any change to the structure of the table didn't automatically break your procedure.
INSERT INTO smp_po_details  
SELECT p.po_number
      ,p.po_date
      ,p.po_quantity
      ,p.po_rate
      ,p.po_status
      ,p.po_totalamount
      ,p.po_pending
      ,v.VENDOR_LOCATION_ITEM_ID
      ,p.po_amendement
FROM   parking_shalina_erp_data p
      ,SMP_VENDOR_LOCATION_ITEM v
where  p.po_number is not null
and    v.VENDOR_ID = to_number(p.vendor_code)
AND    v.itemcode = p.item_code
AND    v.vendor_location_item_id > 0;
Re: Update statement using 2 tables [message #426429 is a reply to message #426428] Thu, 15 October 2009 07:05 Go to previous message
virmahi
Messages: 266
Registered: April 2008
Location: India
Senior Member
Thanks Jrow,
It worked. I was able to do it without loops. Thanks again.

[Updated on: Fri, 16 October 2009 00:13]

Report message to a moderator

Previous Topic: error while running sql query
Next Topic: ORA: 14551 : cannot perform a DML operation inside a query (merged)
Goto Forum:
  


Current Time: Mon Feb 17 17:49:06 CST 2025