1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Updating two tables in single query

Discussion in 'SQL PL/SQL' started by Bharat, Jun 15, 2012.

  1. Bharat

    Bharat Community Moderator Forum Guru

    Messages:
    1,747
    Likes Received:
    147
    Trophy Points:
    1,805
    Location:
    Vijayawada, India
    Hi all,

    My requirement is to update two tables in single query. I have tried using below query but its showing error.

    Code (Text):
    SELECT * FROM DEPT WHERE DEPTNO=50;
    OUTPUT: -
    deptno    dname      location
    50     DEVELOPMENT       AP-IND

    SELECT EMPNO,DEPTNO,SAL FROM EMP WHERE EMPNO=3;
    OUTPUT: -
    EMPNO DEPTNO SAL
    3        50        100
    I have tried to update those two tables using below query but the following error message occurred.

    Code (Text):
    UPDATE (SELECT E.SAL,E.EMPNO,E.ENAME,D.DEPTNO,D.DNAME FROM DEPT d,emp e WHERE EMPNO=3 AND D.DEPTNO=E.DEPTNO)A
     SET A.SAL=3000,A.dname='TESTING';
    before updating sal is 50 and dname is DEVELOPMENT and now updating them to 3000 and TESTING. But after executing the following error is populated.


    After that I have modified update statement and tried to update only dname again as shown below:
    Code (Text):
    UPDATE (SELECT E.SAL,E.EMPNO,E.ENAME,D.DEPTNO,D.DNAME FROM DEPT d,emp e WHERE EMPNO=3 AND D.DEPTNO=E.DEPTNO)A
     SET dname='TESTING';
    but now Iam getting different error as shown below:
    After that I have changed update as below:
    Code (Text):
    UPDATE (SELECT E.SAL,E.EMPNO,E.ENAME,D.DEPTNO,D.DNAME FROM DEPT d,emp e WHERE EMPNO=3 AND D.DEPTNO=E.DEPTNO)A
     SET sal=3000;
    Now this update statement worked fine. If this works fine why my 1st update doesn't working and getting some (view) error.

    Please provide me some solution for this
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,345
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    First you have no keys defined on emp or dept; this prevents the inline view from being key-preserved and thus disallows the update. Adding the necessary keys/unique indexes doesn't help matters as only one table can be updated in a properly created join view:

    Code (SQL):
    SQL> UPDATE (SELECT E.SAL,E.EMPNO,E.ENAME,E.DEPTNO,D.DNAME FROM DEPT d,emp e WHERE EMPNO=3 AND D.DEPTNO=E.DEPTNO) A
      2   SET SAL=3000,dname='TESTING';
     SET SAL=3000,dname='TESTING'
         *
    ERROR at line 2:
    ORA-01779: cannot MODIFY a COLUMN which maps TO a non key-preserved TABLE

    SQL>
    SQL> SELECT E.SAL,E.EMPNO,E.ENAME,E.DEPTNO,D.DNAME FROM DEPT d,emp e WHERE EMPNO=3 AND D.DEPTNO=E.DEPTNO;
     
           SAL      EMPNO ENAME                    DEPTNO DNAME
    ---------- ---------- -------------------- ---------- --------------
          6000          3 Three                         3 TESTING03
     
    SQL>
    SQL> UPDATE (SELECT E.SAL,E.EMPNO,E.ENAME,D.DEPTNO,D.DNAME FROM DEPT d,emp e WHERE EMPNO=3 AND D.DEPTNO=E.DEPTNO)A
      2   SET dname='TESTING';
     SET dname='TESTING'
         *
    ERROR at line 2:
    ORA-01779: cannot MODIFY a COLUMN which maps TO a non key-preserved TABLE

    SQL>
    SQL> SELECT E.SAL,E.EMPNO,E.ENAME,E.DEPTNO,D.DNAME FROM DEPT d,emp e WHERE EMPNO=3 AND D.DEPTNO=E.DEPTNO;
     
           SAL      EMPNO ENAME                    DEPTNO DNAME
    ---------- ---------- -------------------- ---------- --------------
          6000          3 Three                         3 TESTING03
     
    SQL>
    SQL> ALTER TABLE emp ADD CONSTRAINT emp_pk PRIMARY KEY (empno);
     
    TABLE altered.
     
    SQL>
    SQL> ALTER TABLE dept ADD CONSTRAINT dept_pk PRIMARY KEY (deptno);
     
    TABLE altered.
     
    SQL>
    SQL> ALTER TABLE emp ADD CONSTRAINT emp_dept_fk FOREIGN KEY (deptno) REFERENCES dept;
     
    TABLE altered.
     
    SQL>
    SQL> CREATE UNIQUE INDEX dept_dname ON dept(dname);
     
    INDEX created.
     
    SQL>
    SQL> UPDATE (SELECT E.SAL,E.EMPNO,E.ENAME,E.DEPTNO,D.DNAME FROM DEPT d,emp e WHERE EMPNO=3 AND D.DEPTNO=E.DEPTNO) A
      2   SET SAL=3000,dname='TESTING';
     SET SAL=3000,dname='TESTING'
                  *
    [COLOR=red][B]ERROR at line 2:
    ORA-01776: cannot MODIFY more than one base TABLE through a JOIN VIEW[/B][/COLOR]

    SQL>
    SQL> SELECT E.SAL,E.EMPNO,E.ENAME,E.DEPTNO,D.DNAME FROM DEPT d,emp e WHERE EMPNO=3 AND D.DEPTNO=E.DEPTNO;
     
           SAL      EMPNO ENAME                    DEPTNO DNAME
    ---------- ---------- -------------------- ---------- --------------
          6000          3 Three                         3 TESTING03
     
    SQL>
    SQL> UPDATE (SELECT E.SAL,E.EMPNO,E.ENAME,D.DEPTNO,D.DNAME FROM DEPT d,emp e WHERE EMPNO=3 AND D.DEPTNO=E.DEPTNO)A
      2   SET dname='TESTING';
     SET dname='TESTING'
         *
    ERROR at line 2:
    ORA-01779: cannot MODIFY a COLUMN which maps TO a non key-preserved TABLE

    SQL>
    SQL> SELECT E.SAL,E.EMPNO,E.ENAME,E.DEPTNO,D.DNAME FROM DEPT d,emp e WHERE EMPNO=3 AND D.DEPTNO=E.DEPTNO;
     
           SAL      EMPNO ENAME                    DEPTNO DNAME
    ---------- ---------- -------------------- ---------- --------------
          6000          3 Three                         3 TESTING03
     
    SQL>
    This can be done with PL/SQL but it does perform two updates, not one:

    Code (SQL):
    SQL> DECLARE
      2          cursor get_data IS
      3          SELECT e.sal, e.empno, e.ename, e.deptno, d.dname
      4          FROM dept d, emp e
      5          WHERE empno = 3 AND e.deptno = d.deptno;
      6  BEGIN
      7          FOR i IN get_data loop
      8                  UPDATE emp
      9                  SET sal=3000
     10                  WHERE empno = i.empno;
     11
     12                  UPDATE dept
     13                  SET dname = 'TESTING'
     14                  WHERE deptno = i.deptno;
     15
     16          END loop;
     17
     18          commit;
     19
     20  END;
     21  /
     
    PL/SQL PROCEDURE successfully completed.
     
    SQL>
    SQL> SELECT E.SAL,E.EMPNO,E.ENAME,E.DEPTNO,D.DNAME FROM DEPT d,emp e WHERE EMPNO=3 AND D.DEPTNO=E.DEPTNO;
     
           SAL      EMPNO ENAME                    DEPTNO DNAME
    ---------- ---------- -------------------- ---------- --------------
          3000          3 Three                         3 TESTING
     
    SQL>
    Notice also that the view was changed slightly to bring deptno from the emp table instead of the dept table; also note that a unique index was created on the dname column to make dept key-preserved in the view.
     
    Bharat likes this.
  3. Bharat

    Bharat Community Moderator Forum Guru

    Messages:
    1,747
    Likes Received:
    147
    Trophy Points:
    1,805
    Location:
    Vijayawada, India
    Is there any way to update multiple tables in single query. If possible please post it with an example.
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,345
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    No, and I stated that in my prior response. Only one table can be updated at a time in a properly created join view.
     
    Bharat likes this.
  5. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Instead of Trigger could be used in this context. But still it needs two updates.
     
  6. Bharat

    Bharat Community Moderator Forum Guru

    Messages:
    1,747
    Likes Received:
    147
    Trophy Points:
    1,805
    Location:
    Vijayawada, India
    Yes we need to write two update statements when we use Instead of triggers also.