CODEWITHSUNDEEP
sqloracle-database
Omar
Omar

Reputation: 9

update value in table oracle depand on oldest date

I have two tables (T1,T2) connected to each other by foreign key columns (B,C).

T1

C   B   A
11  1   123
12  2   123
13  3   123
14  4   222
15  5   222
16  6   333

T2

A2  B2  C2  D
    1   11  25/4/1972
    2   12  2/11/1982
    3   13  4/6/2000
    4   14  2/7/1992
    5   15  14/2/2010
    6   16  6/3/1999

I need to update A2 value(T2) from A value(T1) according to oldest date in column D (T2) which gives the following result:

T2

A2  B2  C2  D
123 1   11  25/4/1972
    2   12  2/11/1982
    3   13  4/6/2000
222 4   14  2/7/1992
    5   15  14/2/2010
333 6   16  6/3/1999

show resulet

Upvotes: 0

Views: 43

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270583

Assuming that the dates are unique (as in your example), you can do:

update t2
    set t2.a2 = (select t1.a from t1 where t1.b = t2.b2)
    where t2.d = (select min(tt2.d)
                  from t1 join
                       t2 tt2
                       on tt2.b2 = t1.b
                  group by t1.a
                 );

If you do have duplicates, you can change the where to:

where (t2.b2, t2.d) = (select min(tt2.b2) keep (dense_rank first over order by tt2.d), min(tt2.d)
                       from t1 join
                            t2 tt2
                            on tt2.b2 = t1.b
                       group by t1.a
                      )

Upvotes: 1

Littlefoot
Littlefoot

Reputation: 143013

This is far from a smart & nice solution, but - might be OK until someone posts something better.

Test case for the rest of you (saving you some time, as Omar chose not to):

create table t1 (c number, b number, a number);
create table t2 (a2 number, b2 number, c2 number, d date);
insert into t1
  select 11, 1, 123 from dual union
  select 12, 2, 123 from dual union
  select 13, 3, 123 from dual union
  select 14, 4, 222 from dual union
  select 15, 5, 222 from dual union
  select 16, 6, 333 from dual;
insert into t2 (b2, c2, d) 
  select 1, 11, date '1972-04-25' from dual union
  select 2, 12, date '1982-11-02' from dual union
  select 3, 13, date '2000-06-04' from dual union
  select 4, 14, date '1992-07-02' from dual union
  select 5, 15, date '2010-02-14' from dual union
  select 6, 16, date '1999-03-06' from dual;

First update every row, then remove unnecessary values:

SQL> update t2 set
  2    t2.a2 = (select t1.a
  3             from t1
  4             where t1.b = t2.b2
  5               and t1.c = t2.c2
  6            );

6 rows updated.

SQL> update t2 set
  2    t2.a2 = (select distinct
  3               case when min(x.d) over (partition by x.a2) = t2.d then t2.a2
  4                    else null
  5               end
  6             from t2 x
  7             where x.a2 = t2.a2
  8            );

6 rows updated.

SQL> select * from t2;

        A2         B2         C2 D
---------- ---------- ---------- ----------
       123          1         11 25.04.1972
                    2         12 02.11.1982
                    3         13 04.06.2000
       222          4         14 02.07.1992
                    5         15 14.02.2010
       333          6         16 06.03.1999

6 rows selected.

SQL>

Upvotes: 1

Related Questions