Updating two different tables with cursor oracle peter mensch dating

If you’ve been around the Oracle community for some time you’ve probably realized, either through your own experience or by reading articles, if you are able to accomplish a task using a single SQL statement then that is preferred over using…Continue Reading → PL/SQL and SQL both are incredibly powerful languages that allow us to do some pretty amazing things with data.

UPDATE employees e SET employee_salary = ( SELECT MAX(salary) FROM new_salary_info nsi WHERE nsi.employee_id = e.employee_id ) WHERE EXISTS ( SELECT NULL FROM new_salary_info nsi2 WHERE nsi2.employee_id = e.employee_id ); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes |Temp Spc| Cost (%CPU)| Time | -------------------------------------------------------------------------------------------------------- | 0 | UPDATE STATEMENT | | 999K| 17M| | 8007K (13)| | | 1 | UPDATE | EMPLOYEES | | | | | | |* 2 | HASH JOIN SEMI | | 999K| 17M| 23M| 7557 (1)| | | 3 | TABLE ACCESS FULL | EMPLOYEES | 1000K| 12M| | 690 (2)| | | 4 | INDEX FAST FULL SCAN | TEST_IDX | 3996K| 19M| | 2433 (1)| | | 5 | SORT AGGREGATE | | 1 | 13 | | | | | 6 | TABLE ACCESS BY INDEX ROWID| NEW_SALARY_INFO | 4 | 52 | | 7 (0)| | |* 7 | INDEX RANGE SCAN | TEST_IDX | 4 | | | 3 (0)| | -------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("NSI2"."EMPLOYEE_ID"="E"."EMPLOYEE_ID") 7 - access("NSI"."EMPLOYEE_ID"=: B1) UPDATE ( SELECT e.employee_id, e.employee_salary, NVL((SELECT MAX(nsi.salary) FROM new_salary_info nsi WHERE e.employee_id = nsi.employee_id), e.employee_salary) AS new_sal FROM employees e ) SET employee_salary = new_sal; PLAN_TABLE_OUTPUT ----------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------------------- | 0 | UPDATE STATEMENT | | 1000K| 12M| 8000K (13)| | | 1 | UPDATE | EMPLOYEES | | | | | | 2 | TABLE ACCESS FULL | EMPLOYEES | 1000K| 12M| 690 (2)| | | 3 | SORT AGGREGATE | | 1 | 13 | | | | 4 | TABLE ACCESS BY INDEX ROWID| NEW_SALARY_INFO | 4 | 52 | 7 (0)| | |* 5 | INDEX RANGE SCAN | TEST_IDX | 4 | | 3 (0)| | ----------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 5 - access("NSI"."EMPLOYEE_ID"=: B1) MERGE INTO employees e USING ( SELECT e.rowid AS e_rowid, MAX(nsi.salary) AS new_sal FROM employees e, new_salary_info nsi WHERE e.employee_id = nsi.employee_id GROUP BY e.rowid ) new_salaries ON (e.rowid = new_salaries.e_rowid) WHEN MATCHED THEN UPDATE SET e.employee_salary = new_salaries.new_sal; PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes |Temp Spc| Cost (%CPU)| Time | ------------------------------------------------------------------------------------------------------ | 0 | MERGE STATEMENT | | 3991K| 79M| | 14844 (2)| | | 1 | MERGE | EMPLOYEES | | | | | | | 2 | VIEW | | | | | | | | 3 | MERGE JOIN | | 3991K| 144M| | 14844 (2)| | | 4 | SORT JOIN | | 3991K| 95M| | 9392 (3)| | | 5 | VIEW | | 3991K| 95M| | 9392 (3)| | | 6 | SORT GROUP BY | | 3991K| 98M| | 9392 (3)| | |* 7 | HASH JOIN | | 3991K| 98M| 23M| 9228 (1)| | | 8 | INDEX FAST FULL SCAN| SYS_C008302 | 1000K| 12M| | 516 (2)| | | 9 | TABLE ACCESS FULL | NEW_SALARY_INFO | 3996K| 49M| | 2767 (2)| | |* 10 | SORT JOIN | | 1000K| 12M| 45M| 5452 (1)| | | 11 | TABLE ACCESS FULL | EMPLOYEES | 1000K| 12M| | 690 (2)| | ------------------------------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 7 - access("E"."EMPLOYEE_ID"="NSI"."EMPLOYEE_ID") 10 - access("E". ROWID="NEW_SALARIES"."E_ROWID") Those look much better. The optimizer is making use of the new index when it should, and the MERGE statement is now permissible.

2) expression1, expression2, ...expression_n: This specifies the values to assign to the column1, column2, ? This example will update the supplier_name as "Kingfisher" where "supplier_id" is 2.

The following example specifies how to update multiple columns in a table.

Create a stored procedure like this - assuming that pk is the primary key of table1: for select table1.pk, t2.f1, t2.f2 from table1, t2 where table1= t2into :v_pk, :v_f1, :v_f2 do begin update table1 set f1 = :v_f1, f2 = :v_f2 where table1= :v_pk end; source: Ann Harrison on listserv PL/SQL ORACLE PROCEDURE (WALIKHAN09): DECLARE v_F1 T2. F2%TYPE; /* cursor declaration */ CURSOR c_ct IS SELECT FI, F2 FROM T2; BEGIN OPEN c_ct; LOOP FETCH c_ct INTO v_F1, v_F2; /* if there are no more rows to fetch, exit the loop */ EXIT WHEN c_ct%NOTFOUND;/* UPDATING TABLE */UPDATE T1 a SET a.

UPDATE dest_tab tt SET (tt.code, tt.description) = (SELECT st.code, st.description FROM source_tab st WHERE = tt.id) WHERE EXISTS (SELECT 1 FROM source_tab WHERE id = tt.id); 5000 rows updated.

Updating two different tables with cursor oracle