This discussion is archived
1 2 Previous Next 16 Replies Latest reply: Jul 31, 2013 7:04 AM by Suri RSS

Multiple Updates with single stmt

987183 Newbie
Currently Being Moderated

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 Guru
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Guru
    Currently Being Moderated

    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 Guru
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Guru
    Currently Being Moderated

    > 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 Guru
    Currently Being Moderated

    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 Journeyer
    Currently Being Moderated

    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 Expert
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Journeyer
    Currently Being Moderated

    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 Expert
    Currently Being Moderated

    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points