Skip to main content

Update one table from another which had a join

Update First Table Employee_id with 2nd Table Employee ID

 
SET TABLEOUTPUT ON
DECLARE
  CURSOR CUR1
  IS
    SELECT A.ID,
      A.EMPLOYEE_ID
    FROM TABLE2 A,
      WHERE A.EMPLOYEE_NAME= 'NEW';
COUNT_UP NUMBER;
BEGIN
  BEGIN
   COUNT_UP := 0;
    FOR CUR2 IN CUR1
    LOOP
      UPDATE TABLE1
      SET EMPLOYEE_ID = CUR2.EMPLOYEE_ID
      WHERE ID = CUR2.ID;
      COUNT_UP := COUNT_UP + 1;
    END LOOP;
  EXCEPTION
  WHEN OTHERS THEN
    NULL;
  END;
  DBMS_OUTPUT.PUT_LINE('Record Update : '||COUNT_UP);
END;

Comments

Post a Comment

Popular posts from this blog

Insert script with multiple cursors and condition check

DECLARE CURSOR C1 IS   SELECT ID FROM TABLE_NAME_1 WHERE COLUMN IN ('');   CURSOR C2     IS       SELECT ID FROM TABLE_NAME_2 WHERE COLUMN IN ('');              CURSOR C3 (CP_TABLE_01_ID NUMBER,CP_TABLE_02_ID NUMBER)         IS           SELECT COUNT(*) AS COUNT_UP           FROM TABLE_NAME_3           WHERE COLUMN_CONDITION_01=CP_TABLE_02_ID           AND COLUMN_CONDITION_02=CP_TABLE_01_ID; COUNT_UP NUMBER; BEGIN FOR R1 IN C1 LOOP     FOR R2 IN C2     LOOP          OPEN C3(R1.ID,R2.ID);        FETCH C3 INTO COUNT_UP;        CLOSE C3;               IF (COUNT_UP=0) THEN           INSERT           INTO TABL...
Post a Comment
Read more

REF Cursor

REF CURSOR WILL BE DYNAMICALLY OPENS OR OPEN BASED ON A LOGIC. DECLARE TYPE C1 IS REF CURSOR ; CURSOR C IS SELECT * FROM DUAL; REF_CURSOR RC; BEGIN IF (TO_CHAR(SYSDATE, 'DD' ) = 30 ) THEN OPEN REF_CURSOR FOR 'SELECT * FROM TABLE1' ; ELSIF ( TO_CHAR(SYSDATE, 'DD' ) = 29 ) THEN OPEN REF_CURSOR FOR SELECT * FROM TABLE2; ELSE OPEN REF_CURSOR FOR SELECT * FROM DUAL; END IF ; OPEN C; END ;
Post a Comment
Read more