Update values for all rows based on unique ID - Oracle

Question

I have a table:

table1

unique_id       col_id      col_nm      col_val     sequ    s_date      e_date
1               1           test 1      100         1       null        null
1               2           test 2      abc         1       null        null
1               3           test 3      103         1       null        null
1               4           test 4      105         2       null        null
1               5           test 5      10          1       null        null
1               6           my date     20180912    1       null        null
1               7           my date 1   20180913    2       null        null
1               8           test 6      100         1       null        null


2               1           test 1      100         1       null        null
2               2           test 2      abc         1       null        null
2               3           test 3      103         1       null        null
2               4           test 4      105         2       null        null
2               5           test 5      10          1       null        null
2               6           my date     20180911    1       null        null
2               7           my date 1   20180910    2       null        null
2               8           test 6      100         1       null        null

For each unique_id, I need to get col_val and set s_date and e_date:

• s_date = col_val of col_id = 6 and col_nm = 'my date' and sequence = 1
• e_date = col_val of col_id = 7 and col_nm = 'my date 1' and sequence = 2

and then set it for all rows of the appropriate unique_id.

The output table looks like:

unique_id       col_id      col_nm      col_val     sequ    s_date      e_date
1               1           test 1      100         1       20180912    20180913
1               2           test 2      abc         1       20180912    20180913
1               3           test 3      103         1       20180912    20180913
1               4           test 4      105         2       20180912    20180913
1               5           test 5      10          1       20180912    20180913
1               6           my date     20180912    1       20180912    20180913
1               7           my date 1   20180913    2       20180912    20180913
1               8           test 6      100         1       20180912    20180913


2               1           test 1      100         1       20180911    20180910
2               2           test 2      abc         1       20180911    20180910
2               3           test 3      103         1       20180911    20180910
2               4           test 4      105         2       20180911    20180910
2               5           test 5      10          1       20180911    20180910
2               6           my date     20180911    1       20180911    20180910
2               7           my date 1   20180910    2       20180911    20180910
2               8           test 6      100         1       20180911    20180910

My table has over 50 million records, so need the query to take that into consideration.

Is there a way in SQL to achieve this?

Answer 1

You can try the following query:

MERGE INTO TABLE1 T1 USING (
                               SELECT
                                   START_DATE.UNIQUE_ID,
                                   START_DATE.COL_VAL   AS START_DATE,
                                   END_DATE.COL_VAL     AS END_DATE
                               FROM
                                   (
                                       SELECT
                                           T.UNIQUE_ID,
                                           T.COL_VAL
                                       FROM
                                           TABLE1 T
                                       WHERE
                                           T.COL_ID = 6
                                           AND T.COL_NM = 'my date'
                                   ) START_DATE,
                                   (
                                       SELECT
                                           T.UNIQUE_ID,
                                           T.COL_VAL
                                       FROM
                                           TABLE1 T
                                       WHERE
                                           T.COL_ID = 7
                                           AND T.COL_NM = 'my date 1'
                                   ) END_DATE
                               WHERE
                                   START_DATE.UNIQUE_ID = END_DATE.UNIQUE_ID
                           )
DATES ON ( T1.UNIQUE_ID = DATES.UNIQUE_ID ) WHEN MATCHED THEN
UPDATE SET
    T1.S_DATE = DATES.START_DATE,
    T1.E_DATE = DATES.END_DATE;

Demo

Thanks.

Answer 2

You can try to use MERGE with condition aggravated function

MERGE
INTO T trg
USING   (
         SELECT unique_id,
           MAX(CASE WHEN col_id = 6 AND col_nm = 'my date' and sequ = 1 THEN col_val END) mindt,
           MIN(CASE WHEN col_id = 7 AND col_nm = 'my date 1' and sequ = 2 THEN col_val END) maxdt
         FROM T
         GROUP BY unique_id
        ) src
ON  (trg.unique_id = src.unique_id)
WHEN MATCHED THEN UPDATE
    SET trg.e_date = maxdt , trg.s_date = mindt

Results:

| UNIQUE_ID | COL_ID |    COL_NM |  COL_VAL | SEQU |   S_DATE |   E_DATE |
|-----------|--------|-----------|----------|------|----------|----------|
|         1 |      1 |   test 1  |      100 |    1 | 20180912 | 20180913 |
|         1 |      2 |   test 2  |      abc |    1 | 20180912 | 20180913 |
|         1 |      3 |   test 3  |      103 |    1 | 20180912 | 20180913 |
|         1 |      4 |   test 4  |      105 |    2 | 20180912 | 20180913 |
|         1 |      5 |   test 5  |       10 |    1 | 20180912 | 20180913 |
|         1 |      6 |   my date | 20180912 |    1 | 20180912 | 20180913 |
|         1 |      7 | my date 1 | 20180913 |    2 | 20180912 | 20180913 |
|         1 |      8 |   test 6  |      100 |    1 | 20180912 | 20180913 |
|         2 |      1 |   test 1  |      100 |    1 | 20180911 | 20180910 |
|         2 |      2 |   test 2  |      abc |    1 | 20180911 | 20180910 |
|         2 |      3 |   test 3  |      103 |    1 | 20180911 | 20180910 |
|         2 |      4 |   test 4  |      105 |    2 | 20180911 | 20180910 |
|         2 |      5 |   test 5  |       10 |    1 | 20180911 | 20180910 |
|         2 |      6 |   my date | 20180911 |    1 | 20180911 | 20180910 |
|         2 |      7 | my date 1 | 20180910 |    2 | 20180911 | 20180910 |
|         2 |      8 |   test 6  |      100 |    1 | 20180911 | 20180910 |