3 Replies Latest reply: May 16, 2011 8:50 AM by MichaelS RSS

    UPDATE MULTIPLE TABLES IN A SINGLE QUERY

    825873
      Is it possible to update multible tables in a single query using join queries.
        • 1. Re: UPDATE MULTIPLE TABLES IN A SINGLE QUERY
          pollywog
          dont think so
          you can insert into mutliple tables using insert all and insert first
          and you can do multiple operations to the same table using merge
          but I don't think you can do multiple updates to different tables in one shot.
          • 2. Re: UPDATE MULTIPLE TABLES IN A SINGLE QUERY
            Mahir M. Quluzade
            No , You can update single update statement single table.
            But you can update multiple columns
              update table1 set col1='col1 value', col2='col2 value'
            or

            you can use this
            UPDATE t1 t1_alias
            SET (table_name, tablespace_name) = (
              SELECT table_name, tablespace_name
              FROM t2 t2_alias
              WHERE t1_alias.table_name = t2_alias.table_name);
            read this please : http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_10007.htm

            Edited by: Mahir M. Quluzade on May 16, 2011 6:32 PM
            • 3. Re: UPDATE MULTIPLE TABLES IN A SINGLE QUERY
              MichaelS
              Is it possible to update multible tables in a single query using join queries.
              The error message speaks for itself:
              SQL> update (select ename, dname
                        from emp e, dept d
                       where e.deptno = d.deptno
                         and empno = 7788)
                 set ename = 'X', dname = 'Y'
              /
              Error at line 1
              ORA-01776: cannot modify more than one base table through a join view