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!

Update rows with same account id

Discussion in 'SQL PL/SQL' started by tranzeo, Sep 5, 2018.

  1. tranzeo

    tranzeo Starter

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    10
    Location:
    vancouver
    See attached image. [​IMG]
    Need to update 'Beg Balance' of FY 19 to be the same as FY 18.
    Account ID is the unique identifier that can be used as link. Please kindly let me know what will be the SQL script like.

    Thanks.
     

    Attached Files:

    • SQL.JPG
      SQL.JPG
      File size:
      31.1 KB
      Views:
      0
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,618
    Likes Received:
    364
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Ask your instructor for assistance. Do not ask us to write your assignments for you.
     
  3. tranzeo

    tranzeo Starter

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    10
    Location:
    vancouver
    Actually I got this but it doesn't work:
    UPDATE next_year
    SET next_year.[GBAPYC] = prev_year.[GBAPYC]
    FROM "CRPDTA"."F0902" prev_year
    INNER JOIN "CRPDTA"."F0902" next_year
    ON next_year.[GBAID] = prev_year.[GBAID]
    AND prev_year.[GBFY] = 18
    AND next_year.[GBFY] = 19;

    Can you help?
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,618
    Likes Received:
    364
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Why are you equating next_year to prev_year? They will never match. Tell me which column values WILL match between years.
     
  5. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,618
    Likes Received:
    364
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Here is one way:

    Code (SQL):
    UPDATE acct_info ai
    SET beg_bal = (SELECT beg_bal FROM acct_info
                               WHERE acct_no = ai.acct_no
                               AND fy = (SELECT MIN(fy) FROM acct_info)
                               AND beg_bal IS NOT NULL);
     
    Obviously this will need to be changed to update the table you're using in your homework. I leave modifying this sample to you.
     
    tranzeo likes this.