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:

MERGEINTO your_table dst
USING (
  SELECTROW_NUMBER() OVER (
             PARTITIONBY tDate, Product, Price, Quantity, BuySell
             ORDERBY ID
           ) AS idx,
         COUNT( CASE BuySell WHEN'Buy'THEN1END ) OVER (
             PARTITIONBY tDate, Product, Price, Quantity
           ) AS num_buy,
         COUNT( CASE BuySell WHEN'Sell'THEN1END ) OVER (
             PARTITIONBY 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 THENUPDATESET Status ='Matched';

Solution 2:

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

MERGEINTO tablename dst
USING (select t.*,count(*) over(partitionby tDate,Product,Price,Quantity,rn) as cnt 
       from (select t.*,row_number() over(partitionby tDate,Product,Price,Quantity,buysell orderby id) as rn
             from tablename t) t
       ) src
ON (src.id = dst.id AND src.cnt=2)
WHEN MATCHED THENUPDATESET Status ='Matched';

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

select t.*,count(*) over(partitionby tDate,Product,Price,Quantity,rn) as cnt 
from (select t.*,row_number() over(partitionby tDate,Product,Price,Quantity,buysell orderby 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 (orderby accid) as priortime,
      lead(transaction_date||locid, 1) over (orderby accid) as nexttime, 
    lag(srcid, 1) over (orderby accid) as priorsource, 
    lead(srcid, 1) over (orderby 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') orderby 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:

mergeinto mytable t3
using (select t1.*, count(*) over (partitionby tdate,product,price,quantity,field) as field2 from
(
select mytable.*, row_number() over (partitionby mytable.tdate,mytable.product,mytable.price,mytable.quantity,mytable.buysell 
orderby id) field from 
mytable) t1)  t2
on (t2.id=t3.id and t2.field2='2')
when matched thenupdateset status='Matched';