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!

convert merge statement into update statement

Discussion in 'SQL PL/SQL' started by vijayspecial, Nov 13, 2013.

  1. vijayspecial

    vijayspecial Active Member

    Messages:
    9
    Likes Received:
    0
    Trophy Points:
    55
    I have a merge statement see below it is working fine.

    MERGE INTO nss_budget_bk B
    using (
    select OU_CODE,ELEMENT_ID,MONTH9 from NSS_BUDGET_BK where LOAD_ID =136136) E
    ON (B.ou_code = E.ou_code and B.ELEMENT_ID=E.ELEMENT_ID and B.LOAD_ID=142893)
    when matched then
    update set B.MONTH9 = E.MONTH9;

    I need to use update statement instead of merge. i tried the update statement see below but it is not working. Can you help me to change the above merge statement to update statement


    update nss_budget_bk B
    inner join
    select OU_CODE,ELEMENT_ID,MONTH9 from NSS_BUDGET_BK where LOAD_ID =136136) E
    on (B.OU_CODE = E.OU_CODE and B.ELEMENT_ID=E.ELEMENT_ID and B.LOAD_ID=142893)
    set nss_budget_bk.MONTH9=E.MONTH9

    The above update satement showing below error

    SQL Error: ORA-00971: missing SET keyword
    00971. 00000 - "missing SET keyword"
    *Cause:
    *Action:
     
  2. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    What there primary or unique key?
    It is necessary to provide the description of tables

    If everything is defined correctly in a question, it is possible to use with the correlated subquery

    Code (SQL):
    UPDATE nss_budget_bk B
    SET b.MONTH9 =
        (
            SELECT /* if the question is correctly formulated, it is possible to use this option of the operator of update */  
                  e.MONTH9
            FROM
                  NSS_BUDGET_BK e
            WHERE
                  LOAD_ID =136136
                  AND
                  B.OU_CODE = E.OU_CODE
                  AND B.ELEMENT_ID=E.ELEMENT_ID
        )                  
        WHERE  B.LOAD_ID=142893
     

    P.S. http://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_10002.htm#SQLRF55401
     
  3. vijayspecial

    vijayspecial Active Member

    Messages:
    9
    Likes Received:
    0
    Trophy Points:
    55
    Many thanks...

    Load_id is the primary key..

    One more question please..

    I need a generic updation. Can you please give some idea on this. i have gave example below..

    NSS_BUDGET_BK table contain columns
    load_id, month1,month2,month3,month4,,month5,month6,month7,month8,month9,month10,month11,month12

    suppose if table contain data for the column month4,month5,month6 for load_id 142893 we need to update the month4 column using load_id 136136.
    similarly if if table contain data for the column month7,month8,month9 for load_id 142893 we need to update the month6 column using load_id 136136.
    similarly if if table contain data for the column month10,month11,month12 for load_id 142893 we need to update the month9 column using load_id 136136.

    Please note load_id=142893 is current load_id and load_id=136136 is previous load_id

    can you please give some idea how to proceed.

    please let me know if you need further details.
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,345
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Updates like the one you describe don't have generic solutions.

    Of course you can use the LEAD() and LAG() functions to return values either side of a given record:

    Code (SQL):
    SQL> SELECT x.dte, nvl(x.price, lag(x.price) OVER (ORDER BY x.recid)) price
      2  FROM
      3  (SELECT recid, dte, nvl(price, lag(price) OVER (ORDER BY recid)) price
      4  FROM nulltest) x;
     
    DTE            PRICE
    --------- ----------
    01-MAR-11       1234
    04-MAY-11       1344
    11-MAY-11       1344
    13-JUL-11       2569
    23-SEP-11       3865
    24-SEP-11       3865
    03-OCT-11       3865
    16-NOV-11       4568
     
    8 ROWS selected.
     
    SQL>
    SQL> INSERT INTO nulltest
      2  VALUES(0, sysdate, NULL);
     
    1 ROW created.
     
    SQL>
    SQL> SELECT x.dte, nvl(x.price, lead(x.price) OVER (ORDER BY x.recid)) price
      2  FROM
      3  (SELECT recid, dte, nvl(price, lead(price) OVER (ORDER BY recid)) price
      4  FROM nulltest) x;
     
    DTE            PRICE
    --------- ----------
    14-NOV-13       1234
    01-MAR-11       1234
    04-MAY-11       1344
    11-MAY-11       2569
    13-JUL-11       2569
    23-SEP-11       3865
    24-SEP-11       4568
    03-OCT-11       4568
    16-NOV-11       4568
     
    9 ROWS selected.
     
    SQL>
    Possibly you can use one of those functions in your update statement to retrieve prior values.
     
  5. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    multi column update....


    Code (SQL):


    UPDATE nss_budget_bk B
    SET (
         b.month4,b.month5,b.month6,
         b.month7,b.month8,b.month9,
         b.month10,b.month11,b.month12
         ) =
        (
            SELECT
                  e.MONTH4,e.MONTH4,e.MONTH4,
                  e.MONTH6,e.MONTH6,e.MONTH6,
                  e.MONTH9,e.MONTH9,e.MONTH9
            FROM
                  NSS_BUDGET_BK e
            WHERE
                  LOAD_ID =136136
                  AND
                  B.OU_CODE = E.OU_CODE
                  AND B.ELEMENT_ID=E.ELEMENT_ID
        )                  
        WHERE  B.LOAD_ID=142893

     
    p.s.
    http://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_10008.htm
     
  6. vijayspecial

    vijayspecial Active Member

    Messages:
    9
    Likes Received:
    0
    Trophy Points:
    55
    i need to update only one set if condition satifies..
    so can i use if satement rit.
    can you please advise..
     
  7. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    then it is necessary that you gave an example in the table as you need to update writing fields