Updating one table from another

Ok, this is the first time I'm trying something like this, so I don't have a frame of reference to start from.

I hope I am explaining this properly: I have a source of data for equipment.

updating one table from another-86updating one table from another-43

Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).Next, build the query like the one below: This query will update the MFG field in the Bot table with the value in the MFG field in the Big table when the PART values match.I am updating a table (m8054_summary) based on data from another table (temp_summary).Does anyone have any ideas for a better way to do this? When the m8054_table contains its anticipated 95 million plus rows, they will use 19,742 MB of storage. Here is the code: CREATE OR REPLACE PROCEDURE UPDATE_M8054_SUMMARY_FROM_TEMP IS BEGIN DECLARE /* Select information from temp_summary a row at a time */ CURSOR c_temp_summary IS SELECT ser_nbr, batch_id, fare_media_type, issue_llt, validity_period, issue_equip_id, initial_value, io_code, debit_acct_nbr, expire_llt, first_use_llt, last_use_llt, card_status, value_remain, rides_remain, xfers_remain, last_route, total_uses, total_rides, total_xfers, total_value_deduct, total_value_added, total_bonus_added, total_incent_added, total_no_entry, total_free_entry, total_free_xfer, total_test_tckt FROM temp_summary; TYPE t_cursor IS REF CURSOR; v_cursor t_cursor; v_m8054_summary m8054_summary%ROWTYPE; v_ser_nbr NUMBER(12, 0); v_batch_id NUMBER(12, 0); v_fare_media_type NUMBER(3, 0); v_issue_llt DATE; v_validity_period NUMBER(5, 0); v_issue_equip_id VARCHAR2(8); v_initial_value NUMBER(5, 0); v_io_code NUMBER(5, 0); v_debit_acct_nbr NUMBER(12, 0); v_expire_llt DATE; v_first_use_llt DATE; v_last_use_llt DATE; v_card_status NUMBER(3, 0); v_value_remain NUMBER(5, 0); v_rides_remain NUMBER(3, 0); v_xfers_remain NUMBER(3, 0); v_last_route NUMBER(5, 0); v_total_uses NUMBER(5, 0); v_total_rides NUMBER(5, 0); v_total_xfers NUMBER(5, 0); v_total_value_deduct NUMBER(12, 0); v_total_value_added NUMBER(12, 0); v_total_bonus_added NUMBER(12, 0); v_total_incent_added NUMBER(12, 0); v_total_no_entry NUMBER(5, 0); v_total_free_entry NUMBER(5, 0); v_total_free_xfer NUMBER(5, 0); v_total_test_tckt NUMBER(5, 0); v_rows_read NUMBER(12,0); v_rows_inserted NUMBER(12,0); v_rows_updated NUMBER(12,0); BEGIN DBMS_OUTPUT.ENABLE(1000000); OPEN c_temp_summary; v_rows_read := 0; v_rows_inserted := 0; v_rows_updated := 0; LOOP /* Fetch data from temp_summary into cursor */ FETCH c_temp_summary INTO v_ser_nbr, v_batch_id, v_fare_media_type, v_issue_llt, v_validity_period, v_issue_equip_id, v_initial_value, v_io_code, v_debit_acct_nbr, v_expire_llt, v_first_use_llt, v_last_use_llt, v_card_status, v_value_remain, v_rides_remain, v_xfers_remain, v_last_route, v_total_uses, v_total_rides, v_total_xfers, v_total_value_deduct, v_total_value_added, v_total_bonus_added, v_total_incent_added, v_total_no_entry, v_total_free_entry, v_total_free_xfer, v_total_test_tckt; EXIT when c_temp_summary%NOTFOUND; v_rows_read := v_rows_read 1; /* Open dynamic cursor and fetch it to check for existence of row in m8054_summary */ OPEN v_cursor FOR SELECT ser_nbr, batch_id, fare_media_type, issue_llt, validity_period, issue_equip_id, initial_value, io_code, debit_acct_nbr, expire_llt, first_use_llt, last_use_llt, card_status, value_remain, rides_remain, xfers_remain, last_route, total_uses, total_rides, total_xfers, total_value_deduct, total_value_added, total_bonus_added, total_incent_added, total_no_entry, total_free_entry, total_free_xfer, total_test_tckt FROM m8054_summary WHERE ser_nbr = v_ser_nbr; FETCH v_cursor into v_m8054_summary; /* If row does not exist in m8054_summary, insert it */ IF v_cursor%NOTFOUND THEN INSERT INTO m8054_summary VALUES( v_ser_nbr, v_batch_id, v_fare_media_type, v_issue_llt, v_validity_period, v_issue_equip_id, v_initial_value, v_io_code, v_debit_acct_nbr, v_expire_llt, v_first_use_llt, v_last_use_llt, v_card_status, v_value_remain, v_rides_remain, v_xfers_remain, v_last_route, v_total_uses, v_total_rides, v_total_xfers, v_total_value_deduct, v_total_value_added, v_total_bonus_added, v_total_incent_added, v_total_no_entry, v_total_free_entry, v_total_free_xfer, v_total_test_tckt ); v_rows_inserted := v_rows_inserted 1; ELSE /* Otherwise, update it */ UPDATE m8054_summary SET batch_id = v_batch_id, fare_media_type = v_fare_media_type, issue_llt = v_issue_llt, validity_period = v_validity_period, issue_equip_id = v_issue_equip_id, initial_value = v_initial_value, io_code = v_io_code, debit_acct_nbr = v_debit_acct_nbr, expire_llt = v_expire_llt, first_use_llt = v_first_use_llt, last_use_llt = v_last_use_llt, card_status = v_card_status, value_remain = v_value_remain, rides_remain = v_rides_remain, xfers_remain = v_xfers_remain, last_route = v_last_route, total_uses = v_total_uses, total_rides = v_total_rides, total_xfers = v_total_xfers, total_value_deduct = v_total_value_deduct, total_value_added = v_total_value_added, total_bonus_added = v_total_bonus_added, total_incent_added = v_total_incent_added, total_no_entry = v_total_no_entry, total_free_entry = v_total_free_entry, total_free_xfer = v_total_free_xfer, total_test_tckt = v_total_test_tckt; v_rows_updated := v_rows_updated 1; END IF; /* Commit every 10,000 rows */ IF MOD(v_rows_inserted, 10000) = 0 THEN COMMIT; END IF; IF MOD(v_rows_updated, 10000) = 0 THEN COMMIT; END IF; /* Report row number processed every 10,000 rows */ IF MOD(v_rows_inserted, 10000) = 0 THEN DBMS_OUTPUT.

Leave a Reply