1 2 Previous Next 16 Replies Latest reply: Jul 31, 2013 9:04 AM by Suri Go to original post RSS
      • 15. Re: Multiple Updates with single stmt
        Suri

        Hi,

         

        Always mention the table creation and insert statement scripts. And also please mention the business requirement clearly. I am assuming that you have two tables where you need to update the second table based on the first table. In this case we need to compare empno and if it exists we need to update the deptno accordingly.

         

        You can use MERGE to achieve this. Here I have created two sample tables t1 and t2.

        SQL> create table t1 ( empno number
          2                   ,deptno number);

        Table created.

        SQL>
        SQL> create table t2 ( empno number
          2                   ,deptno number);

        Table created.

        SQL>
        SQL> insert into t1 values (1000, 10);

        1 row created.

        SQL>
        SQL> insert into t1 values (1001, 20);

        1 row created.

        SQL>
        SQL> insert into t1 values (1002, 10);

        1 row created.

        SQL>
        SQL> insert into t1 values (1003, 30);

        1 row created.

        SQL>
        SQL> insert into t1 values (1004, 30);

        1 row created.

        SQL>
        SQL> insert into t1 values (1005, 20);

        1 row created.

        SQL>
        SQL>
        SQL> insert into t2 values (1000, null);

        1 row created.

        SQL>
        SQL> insert into t2 values (1001, null);

        1 row created.

        SQL>
        SQL> insert into t2 values (1002, null);

        1 row created.

        SQL>
        SQL> insert into t2 values (1003, null);

        1 row created.

        SQL>
        SQL> insert into t2 values (1004, null);

        1 row created.

        SQL>
        SQL> insert into t2 values (1005, null);

        1 row created.

        SQL>
        SQL> select * from t1;

             EMPNO     DEPTNO
        ---------- ----------
              1000         10
              1001         20
              1002         10
              1003         30
              1004         30
              1005         20

        6 rows selected.

        SQL>
        SQL> select * from t2;

             EMPNO     DEPTNO
        ---------- ----------
              1000
              1001
              1002
              1003
              1004
              1005

        6 rows selected.

        SQL>
        SQL> merge into t2
          2  using t1
          3  on (t1.empno = t2.empno)
          4  when matched then
          5   update set t2.deptno = t1.deptno;

        6 rows merged.

        SQL>
        SQL>  commit;

        Commit complete.

        SQL>
        SQL> -- Desired Output
        SQL>
        SQL>  select * from t2;

             EMPNO     DEPTNO
        ---------- ----------
              1000         10
              1001         20
              1002         10
              1003         30
              1004         30
              1005         20

        6 rows selected.

         

        Hope this helps.

         

        Cheers,

        Suri

        • 16. Re: Multiple Updates with single stmt
          Suri

          Hi,

           

          You may need to use a db link and using MERGE command you can update the VAT IDs based on customer ids. But my sincere advice is use a standard API if you have to update the values. Why becuase if you are working on a ERP then updating a table directly is not a good solution.

           

           

          Cheers,

          Suri

          1 2 Previous Next