Updating table with the earliest date from another table.

Question

IR.DOC_ID D.DOC_ID D.WORK_ID I.WORK_ID  I.DATE_SUB IR.DATE_SUB

1812450   1812450   8521    8521           11-AUG-09

1812301   1812301   8521    8521           11-AUG-09

1812400   1812400   8521    8521           11-AUG-09

1814250   1814250   8521    8521           12-AUG-09

1822300   1822300   8521    8521           18-AUG-09

1814301   1814301   8530    8530           12-AUG-09

1814300   1814300   8530    8530           12-AUG-09

1842250   1842250   8554    8554           08-SEP-09

1888400   1888400   8841    8841           11-JAN-10

1889250   1889250   8841    8841           20-JAN-10

Given the data above, I need to update I.date_sub with the earliest IR.date_sub for that I.work_id (i.e. for work_id 8521, I.date_sub should be 11-AUG-2010).

While the following code works, I was wondering if there's another way of updating the "I" table. I'm not confident with the sorting of the IR.date_sub field.

UPDATE i 
SET i.date_sub = (SELECT min(ir.date_sub) 
FROM ir, d
WHERE ir.doc_id = d.doc_id
AND d.work_id = i.work_id
AND rownum <= 1)

Thanks.

Answer 1

Hi ASC,

your query will update the i.date_sub column with the first value it finds from ir.date_sub because of the predicate rownum <= 1.

If you want to update i.date_sub with the minimum value just remove your predicate:

SQL> UPDATE i
  2     SET i.date_sub = (SELECT MIN(ir.date_sub)
  3                         FROM ir, d
  4                        WHERE ir.doc_id = d.doc_id
  5                          AND d.work_id = i.work_id);

4 rows updated

SQL> select * from i;

   WORK_ID DATE_SUB
---------- -----------
      8521 11/08/2009
      8530 12/08/2009
      8554 08/09/2009
      8841 11/01/2010