Pl sql updating tables from another table Free text sex chat with cam

19-Oct-2019 15:28

December 17, 2009 In a recent comp.databases.Usenet thread: The following question was asked: I need help with a query which involves the 2 tables defined below. CREATE TABLE T1 ( EMP_NO NUMBER, GROUPING VARCHAR2(5), EFF_DATE DATE, DESCR VARCHAR2(5)); CREATE TABLE T2 ( EMP_NO NUMBER, GROUPING VARCHAR2(5), DESCR VARCHAR2(5)); INSERT INTO T1 VALUES (1234,'CI', TO_DATE('01/01/1989','MM/DD/YYYY'),'X'); INSERT INTO T1 VALUES (1234,'CI', TO_DATE('01/02/2000','MM/DD/YYYY'),'X'); INSERT INTO T1 VALUES (1234,'CI', TO_DATE('01/02/2006','MM/DD/YYYY'),'A'); INSERT INTO T1 VALUES (2345,'AF', TO_DATE('01/01/1990','MM/DD/YYYY'),'X'); INSERT INTO T1 VALUES (2345,'AF', TO_DATE('01/02/2005','MM/DD/YYYY'),'A'); INSERT INTO T2 VALUES (1234,'CI','XNN'); INSERT INTO T2 VALUES (2345,'AF','ANN'); INSERT INTO T2 VALUES (3456,'CI','ANN'); COMMIT; SELECT EMP_NO, GROUPING, EFF_DATE, DESCR FROM T1; EMP_NO GROUP EFF_DATE DESCR ------ ----- --------- ----- 1234 CI 01-JAN-89 X 1234 CI 02-JAN-00 X 1234 CI 02-JAN-06 A 2345 AF 01-JAN-90 X 2345 AF 02-JAN-05 ASELECT EMP_NO, GROUPING, EFF_DATE, ROW_NUMBER() OVER (PARTITION BY EMP_NO, GROUPING ORDER BY EFF_DATE DESC) RN, DESCR FROM T1; EMP_NO GROUP EFF_DATE RN DESCR ------ ----- --------- ---------- ----- 1234 CI 02-JAN-06 1 A 1234 CI 02-JAN-00 2 X 1234 CI 01-JAN-89 3 X 2345 AF 02-JAN-05 1 A 2345 AF 01-JAN-90 2 XSELECT EMP_NO, GROUPING, EFF_DATE, DESCR FROM (SELECT EMP_NO, GROUPING, EFF_DATE, ROW_NUMBER() OVER (PARTITION BY EMP_NO, GROUPING ORDER BY EFF_DATE DESC) RN, DESCR FROM T1) WHERE RN=1; EMP_NO GROUP EFF_DATE DESCR ------ ----- --------- ----- 1234 CI 02-JAN-06 A 2345 AF 02-JAN-05 AUPDATE T2 SET DESCR=( SELECT DESCR FROM (SELECT EMP_NO, GROUPING, ROW_NUMBER() OVER (PARTITION BY EMP_NO, GROUPING ORDER BY EFF_DATE DESC) RN, DESCR FROM T1) T1 WHERE RN=1 AND T1. TXT UPDATE T2 SET DESCR=( SELECT DESCR FROM (SELECT EMP_NO, GROUPING, ROW_NUMBER() OVER (PARTITION BY EMP_NO, GROUPING ORDER BY EFF_DATE DESC) RN, DESCR FROM T1) T1 WHERE RN=1 AND T1. I look forward to seeing your future contributions in the comp.databases.oracle.* groups.What I need to do is choose the record with the max “Eff Date” fromTable A” for a particular “Emp No.” and update the “Desc” from that record in the field “Desc” of “Table B” for the same “Emp No.”. (More of Maxim’s solutions may be found in the “SQL – Methods of Reformatting into Equivalent Forms” blog series.) ******************** When posting test case samples, it is much easier for people to reproduce the test case when the SQL*Plus line prefixes are not included - if possible, please remove those line prefixes. Execution Plan ---------------------------------------------------------- Plan hash value: 3235844370 ------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------- | 0 | MERGE STATEMENT | | 2 | 16 | 8 (25)| | | 1 | MERGE | T2 | | | | | | 2 | VIEW | | | | | | |* 3 | HASH JOIN | | 2 | 108 | 8 (25)| | | 4 | TABLE ACCESS FULL | T2 | 3 | 99 | 3 (0)| | | 5 | VIEW | | 5 | 105 | 4 (25)| | | 6 | SORT GROUP BY | | 5 | 150 | 4 (25)| | | 7 | TABLE ACCESS FULL| T1 | 5 | 150 | 3 (0)| | ---------------------------------------------------------------------------­--- Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("T1"."EMP_NO"="T2"."EMP_NO" AND "T1"."GROUPING"="T2"."GROUPING")TRUNCATE TABLE T1; TRUNCATE TABLE T2; INSERT INTO T1 SELECT DECODE(MOD(ROWNUM,10), 0,0000, 1,1111, 2,2222, 3,3333, 4,4444, 5,5555, 6,6666, 7,7777, 8,8888, 9,9999), DECODE(MOD(ROWNUM,6), 0,'AA', 1,'BB', 2,'CC', 3,'DD', 4,'EE', 5,'FF'), TRUNC(SYSDATE SIN(ROWNUM/180*3.141592)*1000), UPPER(DBMS_RANDOM. DISPLAY_CURSOR(NULL, NULL,'ALLSTATS LAST')); SELECT * FROM T2 WHERE LENGTH(DESCR)=1 ORDER BY EMP_NO; ROLLBACK; SPOOL OFFSQL_ID 8w16pv37zxuh5, child number 0 ------------------------------------- UPDATE T2 SET DESCR=( SELECT DESCR FROM (SELECT EMP_NO, GROUPING, ROW_NUMBER() OVER (PARTITION BY EMP_NO, GROUPING ORDER BY EFF_DATE DESC) RN, DESCR FROM T1) T1 WHERE RN=1 AND T1. Best regards Maxim Maxim, A couple people from the OTN forums convinced me to set up this blog – primarily just to reference some of the test cases and demonstrations that have been set up over the years.

Finally, a non-standard way that is the most efficient for Inter Base.

This is a pretty common situation that comes up when performing database operations.

A stored procedure is called and the data needs to be updated if it already exists and inserted if it does not.

UPDATE Table1 SET (...) WHERE Column1='Some Value'IF @@ROWCOUNT=0 INSERT INTO Table1 VALUES (...) The saved table/index scan can increase performance quite a bit as the number of rows in the targeted table grows.

Just remember, the examples in the MSDN documentation are usually the easiest way to implement something, not necessarily the best way.

Finally, a non-standard way that is the most efficient for Inter Base.

This is a pretty common situation that comes up when performing database operations.

A stored procedure is called and the data needs to be updated if it already exists and inserted if it does not.

UPDATE Table1 SET (...) WHERE Column1='Some Value'IF @@ROWCOUNT=0 INSERT INTO Table1 VALUES (...) The saved table/index scan can increase performance quite a bit as the number of rows in the targeted table grows.

Just remember, the examples in the MSDN documentation are usually the easiest way to implement something, not necessarily the best way.

In most standard approaches, the following statement will likely provide better performance.