CODEWITHSUNDEEP
oracle-databaseoracle11goracle10g
Ramya
Ramya

Reputation: 103

Update/Merge a table using sub query in oracle

I have a below table - AccountDetails

 Account_No   Request_Id   Issue_date    Amount   Details
   1              567       20150607      $156     Loan
   2              789       20170406      $765     Personal
   3                        20170216      $897     
   3              987       20160525      $345     Loan
   3              456       20170112      $556     Loan
   4              234       20171118      $987     Loan

I have to update the request_id where request id is null or Details is null for the account with below logic. Need to get the latest request id for the account based on the issue date and have to update the request id (latest request id + 1) WHERE request_id is null or details is null. So the result should be

 Account No   Request_Id   Issue_date    Amount   Details
   1              567       20150607      $156     Loan
   2              789       20170406      $765     Personal
   3              457       20170216      $897     
   3              987       20160525      $345     Loan
   3              456       20170112      $556     Loan
   4              234       20171118      $987     Loan

I tried with the below query

MERGE INTO AccountDetails a
USING ( select Request_Id + 1,ROW_NUMBER() OVER (PARTITION BY B.Account_No 
ORDER BY B.Issue_date desc) AS RANK_NO 
          from AccountDetails ) b
ON ( a.Account_No = b.Account_No AND a.DETAILS IS  NULL)
WHEN MATCHED THEN 
UPDATE SET  a.Request_Id = b.Request_Id
WHERE B.RANK_NO = 1;

Upvotes: 0

Views: 5505

Answers (2)

Boneist
Boneist

Reputation: 23588

Sounds like you need to use the analytic LAG function to determine the previous row's request_id, e.g.:

MERGE INTO account_details tgt
  USING (SELECT account_no,
                CASE WHEN request_id IS NULL THEN 1 + LAG(request_id) OVER (PARTITION BY account_no ORDER BY issue_date)
                     ELSE request_id
                END request_id,
                issue_date,
                amount,
                DETAILS,
                ROWID r_id
         FROM   accountdetails) src
    ON (tgt.rowid = src.r_id)
WHEN MATCHED THEN
  UPDATE SET tgt.request_id = src.request_id;

Of course, this design seems a little odd - why is request_id null in the first place? Is it a unique column? If so, what happens if you end up duplicating an existing request_id with your replacement id? Also, what should happen if it's the first row in for an account number that's got a null request_id?

Upvotes: 2

balaiah
balaiah

Reputation: 1

update accountdetails set request_id=(select max(request_id)+1 from accountdetails) 
   where request_id is null and details is null;

Upvotes: 0

Related Questions