Update statement using 2 tables [message #426425] |
Thu, 15 October 2009 06:45  |
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   |
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;
|
|
|
|