CODEWITHSUNDEEP
oracle-databaseplsql
Baklavaman
Baklavaman

Reputation: 1

Bulk Update from one table to another

So I tried the bulk update in order to copy values from uemte_id column in pp_terminal table to uemte_id column (null at start) in mm_chip table. These two tables have no columns in common.This is what I used:

declare
  type ue_tab is table of  
            pp_terminal.uemte_id%type;

 ue_name ue_tab;
  cursor c1 is select uemte_id from pp_terminal;

begin
 open c1;
fetch c1 bulk collect into ue_name;
 close c1;
 -- bulk insert
forall indx in ue_name.first..ue_name.last
   update mm_chip set uemte_id = ue_name(indx);

end;
/

And this is the error message I get:

Error report:
ORA-00001: unique constraint (DPOWNERA.IX_AK7_MM_CHIP) violated
ORA-06512: at line 13
00001. 00000 -  "unique constraint (%s.%s) violated"
*Cause:    An UPDATE or INSERT statement attempted to insert a duplicate key.
           For Trusted Oracle configured in DBMS MAC mode, you may see
           this message if a duplicate entry exists at a different level.
*Action:   Either remove the unique restriction or do not insert the key.

Do you see any obvious misstakes?

Upvotes: 0

Views: 2556

Answers (2)

Allan
Allan

Reputation: 17429

Below is one way to update each row of one table based on the value of an arbitrary row in a second table, without reusing any rows from the second table. It would perform better if you could do it entirely in SQL, but I couldn't come up with a way to do that.

CREATE TABLE test4 AS
   (SELECT     LEVEL AS cola, CAST(NULL AS number) AS colb
    FROM       DUAL
    CONNECT BY LEVEL <= 100);

CREATE TABLE test5 AS
   (SELECT     100 + LEVEL AS colc
    FROM       DUAL
    CONNECT BY LEVEL <= 99);

DECLARE
   CURSOR cur_test4 IS
      SELECT     *
      FROM       test4
      FOR UPDATE ;
   CURSOR cur_test5 IS
      SELECT * FROM test5;
   r_test5 cur_test5%ROWTYPE;
BEGIN
   OPEN cur_test5;

   FOR r_test4 IN cur_test4 LOOP
      FETCH cur_test5 INTO r_test5;

      IF cur_test5%NOTFOUND THEN
         EXIT;
      END IF;

      UPDATE test4
      SET    colb   = r_test5.colc
      WHERE  CURRENT OF cur_test4;
   END LOOP;

   CLOSE cur_test5;
END;

Upvotes: 0

Jim Hudson
Jim Hudson

Reputation: 8079

What you're trying to do is:

  • select a row from the first table
  • update every row in the second table with that value
  • select another row from the first table
  • update every row in the second table with that value
  • and so forth until the loop finishes

I'm guessing that's not what you really want to do. It's failing because you have a unique constraint so you're not allowed to have multiple rows in the second table with the same value.

Upvotes: 3

Related Questions