update using subquery

[waitecj]

Agreed. Who came up with that crap anyway?

No more of that 01/27/2005 or even 27/01/2005... Why don't we all just use "The twenty seventh of january two thousand and five" and we'll all know where we stand

[DaPi]

Of course if you really want to logical, you'd have to use the ISO format (approx: YYYY-MM-DD HH24:MI:SS) - which nobody uses!

[abhaysk]

Originally posted by padders
If loadfacttable is unique by 'c' you might consider...

UPDATE (
  SELECT f.a, f.b, 
         l.a new_a, l.b new_b
  FROM   loadfacttable l, facttable f
  WHERE  f.c = l.c)
SET a = new_a, b = new_b;

Is their any limitations of this method.. as to the mapping columns in the update list should be one among a KEY?

Abhay.

[slimdave]

Originally posted by abhaysk
Is their any limitations of this method.. as to the mapping columns in the update list should be one among a KEY?

Abhay.

Thinking about it logically, each row in "f" must have no more than a single row in "l", therefore the join between the two must include the PK of "l".

By the way, if this really is a very large fact table then it's going to be much more efficient to create a new table in a nologging mode then replace the existing one with it, 'cos the logging of the updates is going to be a system killer. Assuming that the old fact table is partitioned then this could also be done by replacing the old partitions one-by-one with new data using partition exchanges.

[abhaysk]

Originally posted by slimdave
Thinking about it logically, each row in "f" must have no more than a single row in "l", therefore the join between the two must include the PK of "l".

Well yes.. i would expect that to be so.. else it dosent make much sense..

My Q was not at all realted to that.. Just see the case below..

DBA> ed
Wrote file afiedt.buf

  1  UPDATE
  2    (
  3      SELECT
  4        *
  5      FROM
  6        (
  7        SELECT /*+ Parallel(RPH1, 4) Parallel(RPH2, 4) */
  8          A.PIN       ,
  9          NVL(RPH2.PIN, NVL(RPH1.PIN, 'UNKN')) UPD_PIN
 10        FROM
 11          SHIPMENT_LINE_BO A              ,
 12          REPORTING_PRODUCT_HIER RPH1  ,
 13          REPORTING_PRODUCT_HIER RPH2
 14        WHERE
 15          A.PART_NO                       =     RPH1.PIN(+)    AND
 16          RPH1.PH_LEVEL_NO(+)             =    '8'             AND
 17          A.BASE_PART_NO                  =     RPH2.PIN(+)    AND
 18          RPH2.PH_LEVEL_NO(+)             =    '8'             AND
 19          A.PIN IS NULL
 20        )
 21      WHERE
 22        UPD_PIN != 'UNKN'
 23    )
 24  SET
 25*   PIN = UPD_PIN
DBA> /

0 rows updated.

Elapsed: 00:00:00.60
DBA> ed
Wrote file afiedt.buf

  1  UPDATE
  2    (
  3      SELECT
  4        *
  5      FROM
  6        (
  7        SELECT 
  8            So_Entry_Date,
  9            NVL(D.RECORD_CREATE_DT, '??') Upd_So_Entry_Date
 10        FROM
 11            SHIPMENT_LINE_BO A              ,
 12            SLORDT D
 13        WHERE
 14            A.SALES_ORGANIZATION_CD      =     D.SALES_ORGANIZATION_CD(+)             AND
 15            A.REFERENCE_DOCUMENT_NO      =     D.SALES_ORDER_NO(+)                    AND
 16            A.REFRENCE_DOCUMENT_ITEM_NO  =     D.SALES_ORDER_ITEM_NO(+)               AND
 17            SUBSTR(A.SOURCE_NM,-3,3)     =     SUBSTR(D.SOURCE_NM(+),-3,3)
 18        )
 19      WHERE
 20        Upd_So_Entry_Date != '??'
 21    )
 22  SET
 23*   So_Entry_Date = Upd_So_Entry_Date
DBA> /
  So_Entry_Date = Upd_So_Entry_Date
  *
ERROR at line 23:
ORA-01779: cannot modify a column which maps to a non key-preserved table


Elapsed: 00:00:00.89

Here the "D.RECORD_CREATE_DT" is not a part of any key.. but "RPHx.PIN" is and that update statement ran fine.. So i asked if there is any constraint that the mapping column in update set list should be a part of KEY?

Abhay.

[padders]

> Is their any limitations of this method.. as to the mapping columns in the update list should be one among a KEY?

Yes - as you have shown the join column in the *child* table must be unique with a PK, UK to prove it (although in fact a unique index is sufficient).

If you are feeling brave you can get around this restriction by using the undocumented /*+ BYPASS_UJVC */ hint but if child table has duplicates the rows in the parent table may be updated multiple times in non-deterministic fashion.

Note that the MERGE statement (post 9i) does not work the same way - instead it actually validates that the data is unique rather than checking for the presence of a unique constraint or index. Hence you could do a similar operation without needing a UK, PK or unique index.