1 2 Previous Next 16 Replies Latest reply: Jul 31, 2013 9:04 AM by Suri RSS

    Multiple Updates with single stmt

    987183

      Hi Everyone,

       

      Please help me with this problem.

       

      I have table i.e emp

       

      empno     ename   sal     deptno

      -----     ------  -----   -------

      1          xyz    10000    

      2          xyz1   12000  

      3          xyz3   11000 

      4          xyz4   13000 

      5          xyz5   15000

       

      Now i need to update the deptno based on the empno. i.e my output should be like belows

       

      empno     ename   sal     deptno

      -----     ------  -----   -------

      1          xyz    10000     10

      2          xyz1   12000     20

      3          xyz3   11000     30

      4          xyz4   13000     40

      5          xyz5   15000     50

       

      I should be updatable with single query. Is there any possiblities like that.??

        • 1. Re: Multiple Updates with single stmt
          Frank Kulash

          Hi,

           

          You can UPDATE any number of rows with a single statement.

          To UPDATE every row in the table:

           

          UPDATE  emp

          SET     deptno  = x

          ;

           

          where x is an expression that results in the correct value.  That expression can use any columns on the row.

          For example, if the deptno is always 10 times the empno, then

           

          UPDATE  emp

          SET     deptno  = 10 * empno

          ;

          • 2. Re: Multiple Updates with single stmt
            Oraclenewbie

            Hi,

             

            if this should happend every time you insert a new collum, you could handel this with a trigger.

             

            you could handel it like

             

            empno || 0

            • 3. Re: Multiple Updates with single stmt
              987183

              Hi,

               

              Thank you for your reply.

               

              But the thing is my deptno not like that.

               

              I want output like this,

               

              empno     ename   sal     deptno

              -----     ------  -----   -------

              1          xyz    10000     11

              2          xyz1   12000     12

              3          xyz3   11000     13

              4          xyz4   13000     14

              5          xyz5   15000     15

              • 4. Re: Multiple Updates with single stmt
                Frank Kulash

                Hi,

                 

                If "10 * deptno" is not the correct expression, then use whatever expression is correct.  If you need help in coding that expression, then explain what you want the deptno to be in all cases.

                As long as I don't know where you're going, I can't give you very good directions.

                • 5. Re: Multiple Updates with single stmt
                  SomeoneElse

                  You need to make up your mind:

                   

                  i.e my output should be like belows

                   

                  empno     ename   sal     deptno

                  -----     ------  -----   -------

                  1          xyz    10000     10

                  2          xyz1   12000     20

                  3          xyz3   11000     30

                  4          xyz4   13000     40

                  5          xyz5   15000     50

                   

                   

                  Later...

                   

                  I want output like this,

                   

                  empno     ename   sal     deptno

                  -----     ------  -----   -------

                  1          xyz    10000     11

                  2          xyz1   12000     12

                  3          xyz3   11000     13

                  4          xyz4   13000     14

                  5          xyz5   15000     15

                  • 6. Re: Multiple Updates with single stmt
                    987183

                    Hi,

                     

                    Here the thing is i have imported Customer Table from one instance to another instance. In that one column i.e VAT_id was missed.

                     

                    Now i need to update that VAT_id's based on that customer_id in the destination instance.

                     

                    For that i need  update stmt which should be update the same VAT_id's what are there in the source instance into destination instance.

                    • 7. Re: Multiple Updates with single stmt
                      SomeoneElse

                      > For that i need  update stmt which should be update the same VAT_id's what are there in the source instance into destination instance.

                       

                      This is NOTHING like you described in your first post.

                       

                      Please post all relevant tables (with CREATE TABLE statements and INSERT statements for sample data) and what you really want to do.

                      • 8. Re: Multiple Updates with single stmt
                        Frank Kulash

                        Hi,

                         

                        How are customer_id and vat_id related to empno?

                        Is there more than one table in in this problem?

                         

                        Please post CREATE TABLE and INSERT statements for all tables involved, as they exist before the UPDATE.  Also post the results you want from that data, that is, the contents of the changed table after the UPDATE.  Explain, with specific examples, how you get those results from the given data.

                        Always say which version of Oracle you're using (e.g., 11.2.0.2.0).

                        See the forum FAQ: https://forums.oracle.com/message/9362002

                        • 9. Re: Multiple Updates with single stmt
                          Ishan

                          You can use MERGE command.

                           

                          MERGE INTO emp e

                            USING cust_destination h

                            ON (e.customer_id = h.customer_id) -- This is the condition on which you need to join the two tables

                            WHEN MATCHED THEN

                            UPDATE SET e.deptno = h.deptno

                            ;

                           

                          You need to be careful with the duplicates in the join condition.

                          This should do the job. Can't give you the exact query as I don't have the complete structure of the tables.

                           

                          Thanks,

                          Ishan

                          • 10. Re: Multiple Updates with single stmt
                            Greg Spall

                            Totally wild guess here, but are you trying to do something like this?? (ie you have a 2nd table you're not telling us about which holds the mappings?)

                             

                            create table  junk1
                               (id   number,
                                ename  varchar2(20),
                                val   number );
                               
                            Table created.
                            
                            insert into junk1
                                (
                                     select 1 id, 'xyz ' ename, null val from dual union all   
                                     select 2   , 'xyz1'      , null     from dual union all 
                                     select 3   , 'xyz3'      , null     from dual union all
                                     select 4   , 'xyz4'      , null     from dual union all
                                     select 5   , 'xyz5'      , null     from dual
                                   );
                               
                            5 rows created.
                            
                            create table junk2
                               (id   number,
                                val  number );
                               
                            Table created.
                            
                            insert into junk2
                                (
                                     select 1 id, 11 val from dual union all   
                                     select 2   , 123     from dual union all 
                                     select 3   , 33     from dual union all
                                     select 4   , 944     from dual union all
                                     select 5   , 55     from dual
                                   );
                                  
                            5 rows created.
                            
                            update junk1 j1
                               set val = ( select val from junk2 j2
                                              where j2.id = j1.id );
                                             
                            5 rows updated.
                            
                            Select * from junk1;
                            
                                    ID ENAME                       VAL
                            ---------- -------------------- ----------
                                     1 xyz                          11
                                     2 xyz1                        123
                                     3 xyz3                         33
                                     4 xyz4                        944
                                     5 xyz5                         55
                            
                            5 rows selected.
                            
                            • 11. Re: Multiple Updates with single stmt
                              987183

                              Table Name: Customer_details_tab     Which is in source instance (11i)

                              Customer_id         customer_number        Customer_name       vat_id

                              ------------------          --------------------------        -----------------------       -----------

                              1241                      C12121212                  Dell                          1001

                              1242                      C12121213                  Lenovo                      1003

                              1243                      C12121214                  HCL                          1004

                              1244                      C12121215                  Toshiba                     1005

                              1245                      C12121216                  Samsung                   1006

                               

                              Table Name:   Customer_details_tab           Which is in destination instance (R12).

                               

                              Customer_id         customer_number        Customer_name       vat_id

                              ------------------          --------------------------        -----------------------       -----------

                              1241                      C12121212                  Dell                        

                              1242                      C12121213                  Lenovo                     

                              1243                      C12121214                  HCL                         

                              1244                      C12121215                  Toshiba                    

                              1245                      C12121216                  Samsung                  

                               

                              Now i need to update the same vat_id values into destination instance(R12).

                               

                              For that how i can achieve this one with the help of Update stmt. Suggest me the another possible ways if have.

                              • 12. Re: Multiple Updates with single stmt
                                987183

                                Table Name: Customer_details_tab     Which is in source instance (11i)

                                Customer_id         customer_number        Customer_name       vat_id

                                ------------------          --------------------------        -----------------------       -----------

                                1241                      C12121212                  Dell                          1001

                                1242                      C12121213                  Lenovo                      1003

                                1243                      C12121214                  HCL                          1004

                                1244                      C12121215                  Toshiba                     1005

                                1245                      C12121216                  Samsung                   1006

                                 

                                Table Name:   Customer_details_tab           Which is in destination instance (R12).

                                 

                                Customer_id         customer_number        Customer_name       vat_id

                                ------------------          --------------------------        -----------------------       -----------

                                1241                      C12121212                  Dell                        

                                1242                      C12121213                  Lenovo                     

                                1243                      C12121214                  HCL                         

                                1244                      C12121215                  Toshiba                    

                                1245                      C12121216                  Samsung                  

                                 

                                Now i need to update the same vat_id values into destination instance(R12).

                                 

                                For that how i can achieve this one with the help of Update stmt. Suggest me the another possible ways if have.

                                • 13. Re: Multiple Updates with single stmt
                                  Ishan

                                  Ok.

                                   

                                  Merge command that I wrote above is the best fit here. Did you try it out? If yes and if it failed, can you post the error?

                                  • 14. Re: Multiple Updates with single stmt
                                    Greg Spall

                                    Yeah, I think my query I posted is on the right track then.  You have one table acting as a "mapping table", the other you want to populate.

                                     

                                    The update should get you started (might need to tweak it to account for records not found in one or the other - MERGE might be more useful at that point if it gets complicated).

                                     

                                    If the table to update is vat_id all NULLs, though, you're probably good with the an update like I posted .

                                    1 2 Previous Next