m7nRZH2X

1Bulk Update from one table to another
2declare
3  type ue_tab is table of  
4            pp_terminal.uemte_id%type;
5
6 ue_name ue_tab;
7  cursor c1 is select uemte_id from pp_terminal;
8
9begin
10 open c1;
11fetch c1 bulk collect into ue_name;
12 close c1;
13 -- bulk insert
14forall indx in ue_name.first..ue_name.last
15   update mm_chip set uemte_id = ue_name(indx);
16
17end;
18/
19	
20Error report:
21ORA-00001: unique constraint (DPOWNERA.IX_AK7_MM_CHIP) violated
22ORA-06512: at line 13
2300001. 00000 -  "unique constraint (%s.%s) violated"
24*Cause:    An UPDATE or INSERT statement attempted to insert a duplicate key.
25           For Trusted Oracle configured in DBMS MAC mode, you may see
26           this message if a duplicate entry exists at a different level.
27*Action:   Either remove the unique restriction or do not insert the key.
28	
29CREATE TABLE test4 AS
30   (SELECT     LEVEL AS cola, CAST(NULL AS number) AS colb
31    FROM       DUAL
32    CONNECT BY LEVEL <= 100);
33
34CREATE TABLE test5 AS
35   (SELECT     100 + LEVEL AS colc
36    FROM       DUAL
37    CONNECT BY LEVEL <= 99);
38
39DECLARE
40   CURSOR cur_test4 IS
41      SELECT     *
42      FROM       test4
43      FOR UPDATE ;
44   CURSOR cur_test5 IS
45      SELECT * FROM test5;
46   r_test5 cur_test5%ROWTYPE;
47BEGIN
48   OPEN cur_test5;
49
50   FOR r_test4 IN cur_test4 LOOP
51      FETCH cur_test5 INTO r_test5;
52
53      IF cur_test5%NOTFOUND THEN
54         EXIT;
55      END IF;
56
57      UPDATE test4
58      SET    colb   = r_test5.colc
59      WHERE  CURRENT OF cur_test4;
60   END LOOP;
61
62   CLOSE cur_test5;
63END;