Hi,

I need help with a query which involves the 2 tables defined below.
What I need to do is choose the record with the max “Eff Date” from
“Table A” for a particular “Emp No.” and update the “Desc” fromthat
record in the field “Desc” of “Table B” for the same “Emp No.”.I am
able to choose the max “Eff Date” record for each employee from Table
A but somehow not able to updated the same “Desc” in “Table B”.

Request you to please help the query. Any help would be appreciated.
Thanks!

Table A
Emp No. Group Eff Date Desc
1234 CI 01/01/1989 X
1234 CI 01/02/2000 X
1234 CI 01/02/2006 A
2345 AF 01/01/1990 X
2345 AF 01/02/2005 A

Table B
Emp No. Group Desc
1234 CI X
2345 AF A
3456 CI A

On Dec 15, 6:00*am, Sandy80 <svarshneym... (AT) gmail (DOT) com> wrote:
When requesting assistance, please provide the necessary DDL and DML
to create the test case. Also, post what you have tried so far.

Watch the query and results closely as one possible solution is built
(there are other methods):
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,
DESCR
FROM
T2;

EMP_NO GROUP DESCR
------ ----- -----
1234 CI XNN
2345 AF ANN
3456 CI ANN


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 A


SELECT
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 X


SELECT
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 A


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.EMP_NO=T2.EMP_NO
AND T1.GROUPING=T2.GROUPING)
WHERE
(T2.EMP_NO,T2.GROUPING) IN (
SELECT
EMP_NO,
GROUPING
FROM
(SELECT
EMP_NO,
GROUPING,
ROW_NUMBER() OVER (PARTITION BY EMP_NO, GROUPING ORDER BY
EFF_DATE DESC) RN,
DESCR
FROM
T1)
WHERE
RN=1);

2 rows updated.


SELECT
EMP_NO,
GROUPING,
DESCR
FROM
T2;

EMP_NO GROUP DESCR
------ ----- -----
1234 CI A
2345 AF A
3456 CI ANN

Note that in the above, I assumed that the combination of EMP_NO and
GROUPING had to be the same.

Charles Hooper
Co-author of "Expert Oracle Practices: Oracle Database Administration
from the Oak Table"
http://hoopercharles.wordpress.com/
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.

On 15.12.2009 16:04, Charles Hooper wrote:
Alternatively, ( of course, if the version allows), update path of merge
can be used:

SQL> merge into t2 t2
2 using (
3 select emp_no,grouping,
4 max(descr) keep(dense_rank last order by eff_date) descr
5 from t1 group by emp_no,grouping) t1
6 on (t1.emp_no=t2.emp_no
7 and t1.grouping=t2.grouping)
8 when matched then update set t2.descr=t1.descr
9 ;

2 rows merged.


Execution Plan
----------------------------------------------------------
Plan hash value: 3235844370

-------------------------------------------------------------------------------
-------------------------------------------------------------------------------
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

3 - access("T1"."EMP_NO"="T2"."EMP_NO" AND
"T1"."GROUPING"="T2"."GROUPING")


Best regards

Maxim