Tuesday, June 23, 2015

How to Update One table from Another tables data which have join

We want to update First table TICKET_ID from second table TICKET_ID where TICKET_NAME = 'NEW',
we should have to update first table ( ID ) matching data to second table (U_ID).

CREATE TABLE TABLE1
(ID,F_NAME,L_NAME,TICKET_ID);

CREATE TABLE TABLE2

(T_ID,TICKET_ID,U_ID,TICKET_NAME);


SET SERVEROUTPUT ON

DECLARE
  CURSOR C1
  IS
    SELECT A.ID,
      A.TICKET_ID
    FROM TABLE2 A,
      WHERE A.TICKET_NAME = 'NEW';
COUNT_UP NUMBER;
BEGIN
  BEGIN
   COUNT_UP := 0;
    FOR C2 IN C1
    LOOP
      UPDATE TABLE1
      SET TICKET_ID   = C2.TICKET_ID
      WHERE ID = C2.U_ID;
      COUNT_UP := COUNT_UP + 1;
    END LOOP;
  EXCEPTION
  WHEN OTHERS THEN
    NULL;
  END;
  DBMS_OUTPUT.PUT_LINE('Number of Record Update : '||COUNT_UP);
END;

No comments:

Post a Comment