Compare Rows In Oracle Table And Update Matching Ones

I have a table such as the following: **ID tDate Product Price Quantity BuySell Status** 1 10-May-17 pppp $12 20 Buy

Solution 1:

Untested but something like this using only SQL:

MERGE INTO your_table dst
USING (
  SELECT ROW_NUMBER() OVER (
             PARTITION BY tDate, Product, Price, Quantity, BuySell
             ORDER BY ID
           ) AS idx,
         COUNT( CASE BuySell WHEN 'Buy' THEN 1 END ) OVER (
             PARTITION BY tDate, Product, Price, Quantity
           ) AS num_buy,
         COUNT( CASE BuySell WHEN 'Sell' THEN 1 END ) OVER (
             PARTITION BY tDate, Product, Price, Quantity
           ) AS num_sell
  FROM   your_table
) src
ON ( src.ROWID = dst.ROWID AND src.idx <= LEAST( src.num_buy, src.num_sell ) )
WHEN MATCHED THEN
  UPDATE SET Status = 'Matched';

Solution 2:

You can get the number of buy-sell pairs per tdate and update such rows.

MERGE INTO tablename dst
USING (select t.*,count(*) over(partition by tDate,Product,Price,Quantity,rn) as cnt 
       from (select t.*,row_number() over(partition by tDate,Product,Price,Quantity,buysell order by id) as rn
             from tablename t) t
       ) src
ON (src.id = dst.id AND src.cnt=2)
WHEN MATCHED THEN
UPDATE SET Status = 'Matched';

Run this query to see how row numbers are assigned to buy-sell.

select t.*,count(*) over(partition by tDate,Product,Price,Quantity,rn) as cnt 
from (select t.*,row_number() over(partition by tDate,Product,Price,Quantity,buysell order by id) as rn
      from tablename t) t

Solution 3:

here's another perspective to add to the others. This addresses only the matching portion and not the update or merge part. I had a similar problem recently where I needed to find records that matched on transaction date and location, but came from two different sources. In this case the records have to already be sorted so that the like records will be together. The inner query compares the record to the one before and the one after and grabs it if they match. Then the outer query determines if they meet the 'difference' criteria. Hope this helps.

select sbs.trnsid, sbs.amount, sbs.transaction_date, sbs.posted_date, sbs.srcid, 
        sbs.credited_flag, sbs.accid, sbs.compid, sbs.badgeid, sbs.locid, sbs.date_credited, 
        sbs.searchable, sbs.priortime, sbs.nexttime, sbs.priorsource, sbs.nextsource 
    from 
    (select trnsid, amount, transaction_date, posted_date, srcid, credited_flag,
      accid, compid, badgeid, locid, date_credited, transaction_date||locid as searchable,
      lag(transaction_date||locid, 1) over (order by accid) as priortime,
      lead(transaction_date||locid, 1) over (order by accid) as nexttime, 
    lag(srcid, 1) over (order by accid) as priorsource, 
    lead(srcid, 1) over (order by accid) as nextsource
    from transactions_table
    where accid = v_acct
      and transaction_date >= to_date('10/01/2016 00:00:00', 'mm/dd/yyyy hh24:mi:ss') 
      and transaction_date <= to_date('04/23/2017 23:59:59', 'mm/dd/yyyy hh24:mi:ss')
      and srcid in ('B', 'S') order by accid, transaction_date, locid) sbs
    where (sbs.searchable = sbs.nexttime and sbs.srcid = 'S' and sbs.nextsource = 'B')
   or (sbs.searchable = sbs.priortime and sbs.srcid = 'B' and sbs.priorsource = 'S');

Solution 4:

merge into mytable t3
using (select t1.*, count(*) over (partition by tdate,product,price,quantity,field) as field2 from
(
select mytable.*, row_number() over (partition by mytable.tdate,mytable.product,mytable.price,mytable.quantity,mytable.buysell 
order by id) field from 
mytable) t1)  t2
on (t2.id=t3.id and t2.field2='2')
when matched then 
update set status='Matched';