Skip to Main Content
  • Questions

  • update or update insert based on the status value column

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, CP.

Asked: April 01, 2016 - 8:21 pm UTC

Last updated: April 05, 2016 - 1:42 am UTC

Version: 12C

Viewed 1000+ times

You Asked

I am an not able to build a logic in the below scenario.

I have a table MY_TRANSACTIONS with below data

TXN_ID ACCOUNT OWNER DATE AMOUNT STATUS BEGIN_DT END_DT
1 123 TOM 2/1/16 100 APPROVED 2/1/16
2 123 TOM 2/2/16 200 REJECTED 2/2/16
3 123 TOM 2/2/16 150 PENDING 2/3/16

it has the unique constraint on the (ACCOUNT, DATE,STATUS, END_DT)

I need to implement the logic as below with an array of data as input.
- If the user is trying to approve an pending record I have to update the PENDING status as APPROVED.
- If the user is trying to reject an APPROVED record I have to update the END_DT for that approved record and INSERT a new record with STATUS AS REJECTED.

Here is the Input I will be getting

ACCOUNT OWNER DATE AMOUNT STATUS
123 TOM 2/1/16 100 REJECTED
123 CHRIS 2/5/16 500 PENDING
123 TOM 2/2/16 150 APPROVED


Please let me know if you need any further details.

and Connor said...

A set of test case scripts would have been useful.....sigh.

Anyway, try something like this


SQL> drop table MY_TRANSACTIONS purge;

Table dropped.

SQL> create table MY_TRANSACTIONS (
  2  seq int, txn_id int, account varchar2(10), dte date, amt int, status varchar2(20), begin_dt date, end_dt date );

Table created.

SQL>
SQL>
SQL> insert into MY_TRANSACTIONS values (1, 123 ,'TOM', '2-feb-16', 100 ,'APPROVED', '1-feb-16',null );

1 row created.

SQL> insert into MY_TRANSACTIONS values (2, 123 ,'TOM', '2-feb-16', 200 ,'REJECTED', '2-feb-16',null);

1 row created.

SQL> insert into MY_TRANSACTIONS values (3, 123 ,'TOM', '2-feb-16', 150 ,'PENDING' ,'3-feb-16',null);

1 row created.

SQL>
SQL> drop table gtt purge;

Table dropped.

SQL>
SQL> create global temporary table gtt (seq int, txn_id int, account varchar2(10), dte date, amt int, status varchar2(20) );

Table created.

SQL>
SQL> insert into gtt values (1,123 ,'TOM', '2-feb-16', 100, 'REJECTED');

1 row created.

SQL> insert into gtt values (2,123 ,'CHRIS', '5-feb-16', 500, 'PENDING');

1 row created.

SQL> insert into gtt values (3,123 ,'TOM', '2-feb-16', 150, 'APPROVED ');

1 row created.

SQL>
SQL> update MY_TRANSACTIONS
  2  set status = 'APPROVED'
  3  where status = 'PENDING'
  4  and (txn_id,account) in ( select txn_id,account from gtt where status = 'APPROVED' );

0 rows updated.

SQL>
SQL>
SQL> update MY_TRANSACTIONS m
  2  set end_dt =  ( select dte from gtt where txn_id = m.txn_id and account = m.account and m.status = 'REJECTED' )
  3  where status = 'APPROVED'
  4  and (txn_id,account) in ( select txn_id,account from gtt where status = 'REJECTED' );

1 row updated.

SQL>
SQL> insert into MY_TRANSACTIONS
  2  select seq , txn_id , account , dte , amt , status, dte, null
  3  from gtt where status = 'REJECTED';

1 row created.

SQL>
SQL> select * from MY_TRANSACTIONS;

       SEQ     TXN_ID ACCOUNT    DTE              AMT STATUS               BEGIN_DT  END_DT
---------- ---------- ---------- --------- ---------- -------------------- --------- ---------
         1        123 TOM        02-FEB-16        100 APPROVED             01-FEB-16
         2        123 TOM        02-FEB-16        200 REJECTED             02-FEB-16
         3        123 TOM        02-FEB-16        150 PENDING              03-FEB-16
         1        123 TOM        02-FEB-16        100 REJECTED             02-FEB-16

SQL>


Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

But this has to be done in a loop

CP, April 04, 2016 - 1:14 pm UTC

Thanks for your support.
But here I will be getting the data in an array which I have to loop in for update / insert.

Here is the logic which I have thought of..

For i IN 1..p_array.LAST
LOOP
CASE
WHEN p_array(i).status = 'APPROVED' THEN
UPDATE MY_TRANSACTIONS
SET STATUS = 'APPROVED'
WHERE (TXN_ID,ACCOUNT) IN (p_array(i).txn_id,p_array(i).account)
AND status = 'PENDING';
WHEN p_array(i).status = 'REJECTED' THEN
UPDATE MY_TRANSACTIONS
SET STATUS = 'REJECTED'
AND END_DT = SYSDATE
WHERE (TXN_ID,ACCOUNT) IN (p_array(i).txn_id,p_array(i).account)
AND status = 'APPROVED';
-- insert a record with reject status.


Can you please validate and help me proceed further.
Thanks a lot for your support.
Connor McDonald
April 05, 2016 - 1:42 am UTC

Yes you could do that now that you've *told* us how you are getting the input - my crystal ball is broken at the moment :-)

In such a case (depending on the number of array entries you expect), you might be better off using a nested table so you can do the updates en masse via SQL.

No two updates, just one should be fine.

Rajeshwaran Jeyabal, April 04, 2016 - 2:00 pm UTC 

update my_transactions
set status = case when status ='PENDING' then 'APPROVED'
    when status = 'APPROVED' then 'REJECTED' end
where (TXN_ID,ACCOUNT) IN (p_array(i).txn_id,p_array(i).account) 
AND status in ('APPROVED' ,'PENDING');

A new record has to be inserted if Approved is Rejected

CP, April 04, 2016 - 5:44 pm UTC

Thanks for your review.
But here, the existing approved record should be made as in active by end date and a new record with rejected status has to be inserted with same data.
Chris Saxon
April 05, 2016 - 1:41 am UTC

You could alter Rajesh's update to be something like:

update my_transactions
set status = case when status ='PENDING' then 'APPROVED'
          when status = 'APPROVED' then 'REJECTED' end,
    end_dt =  case when status  ='PENDING' then end_dt -- ie, no change
           when status = 'APPROVED' then ( select dte from gtt 
                where txn_id = m.txn_id 
                and account = m.account and m.status = 'REJECTED' )
        end
where (TXN_ID,ACCOUNT) IN (p_array(i).txn_id,p_array(i).account) 
AND status in ('APPROVED' ,'PENDING');

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.