CODEWITHSUNDEEP
sqloracle-database
Drew
Drew

Reputation: 1

Update between 2 tables with a subquery for the 2nd highest date row

I'm trying to update all values of a column in a table 1 using a value from table 2 that has a corresponding id. The tricky part is that the value I need to pull from table 2 needs to be the 2nd highest date row, which I can use a date column for the 2nd highest date in the table.

This is what I have tried, but this is not an efficient query. I'm wondering if there is a more efficient way of going about this to achieve my goal.

UPDATE table1 tb1
SET newVal = (SELECT DISTINCT val
           FROM tb2
           WHERE tb1.id = tb2.id
           AND date = 
                  (SELECT MAX(date)
                   FROM tb2
                   WHERE tb1.id = tb2.id
                   AND date <
                          (SELECT MAX(date)
                           FROM tb2
                           WHERE tb1.id = tb2.id))),
update_time = SYSDATE
WHERE newVal IS NULL
AND EXISTS (SELECT DISTINCT val
           FROM tb2
           WHERE tb1.id = tb2.id
           AND date = 
                  (SELECT MAX(date)
                   FROM tb2
                   WHERE tb1.id = tb2.id
                   AND date <
                          (SELECT MAX(date)
                           FROM tb2
                           WHERE tb1.id = tb2.id)));

I may be attempting something that is not possible in a single query but any help would be greatly appreciated.

Thanks!

Upvotes: 0

Views: 36

Answers (1)

MT0
MT0

Reputation: 168361

You can use a MERGE statement and the DENSE_RANK analytic function:

MERGE INTO table1 t1
USING (
  SELECT id,
         val,
         ROW_NUMBER() OVER (PARTITION BY id ORDER BY val DESC) AS rn
  FROM   (
    SELECT id,
           val,
           DENSE_RANK() OVER (PARTITION BY id ORDER BY "DATE" DESC) AS rnk
    FROM   table2
  )
  WHERE  rnk = 2
) t2
ON (t1.id = t2.id AND t2.rn = 1)
WHEN MATCHED THEN
  UPDATE
  SET newval = t2.val
  WHERE newval IS NULL;

Which, for the sample data:

CREATE TABLE table1 (id, newval) AS
SELECT 1, NULL FROM DUAL UNION ALL
SELECT 2, NULL FROM DUAL UNION ALL
SELECT 3, NULL FROM DUAL UNION ALL
SELECT 4, 4 FROM DUAL UNION ALL
SELECT 5, NULL FROM DUAL;

CREATE TABLE table2 (id, "DATE", val) AS
SELECT 1, DATE '2022-01-01', 1.1 FROM DUAL UNION ALL
SELECT 1, DATE '2022-01-02', 1.2 FROM DUAL UNION ALL
SELECT 1, DATE '2022-01-03', 1.3 FROM DUAL UNION ALL
SELECT 2, DATE '2022-01-01', 2.1 FROM DUAL UNION ALL
SELECT 2, DATE '2022-01-02', 2.2 FROM DUAL UNION ALL
SELECT 3, DATE '2022-01-01', 3.1 FROM DUAL UNION ALL
SELECT 4, DATE '2022-01-01', 4.1 FROM DUAL UNION ALL
SELECT 4, DATE '2022-01-02', 4.2 FROM DUAL UNION ALL
SELECT 5, DATE '2022-01-01', 5.1 FROM DUAL UNION ALL
SELECT 5, DATE '2022-01-01', 5.2 FROM DUAL UNION ALL
SELECT 5, DATE '2022-01-02', 5.3 FROM DUAL UNION ALL
SELECT 5, DATE '2022-01-02', 5.4 FROM DUAL;

Then, after the MERGE, table1 contains:

ID NEWVAL
1 1.2
2 2.1
3 null
4 4
5 5.2

db<>fiddle here

Upvotes: 1

Related Questions