CODEWITHSUNDEEP
sqlsql-serversql-updateauto-increment
user641605
user641605

Reputation: 421

Update first row with oldest, the next with second oldest, and so forth

I'm having trouble writing an update in SQL Server with no relations. I have looked all over the forum but I have a hard time to find the answer.

I need to update the OCR from if the Customer, Amount is matching. The problem is that this is not a unique key. So, if there is more then one record from the customer, amount combination, I need to take the oldest match in the lookup table and update the OCR from it. Then I need to take the second oldest and update the second row with its OCR.

I tried to visualize it in the tables below.

All suggestions are welcome!

Table to update - before update 

Customer       OCR  Amount      
740000010417        220.000     
740000010417        220.000     
740000010421        300.000     
740000010421        250.000

Lookup Table

Customer         OCR            Amount  Date                    ID
740000010417    222357110626    220.000 2011-11-11 15:48:48.510 100642
740000010417    222350553822    220.000 2011-10-18 10:10:26.210 18680
740000010417    222350464525    220.000 2011-10-18 10:10:26.210 18681
740000010417    222357110725    220.000 2011-11-11 15:48:48.510 102547
740000010421    222357127726    250.000 2011-11-11 15:48:48.510 102548
740000010421    222357127725    220.000 2011-10-19 10:10:26.210 102549
740000010421    222357130555    250.000 2011-10-19 10:10:26.210 102550

Table after update 

Customer            OCR          Amount     
740000010417    222350553822    220.000     
740000010417    222350464525    220.000     
740000010421                    300.000     
740000010421    222357130555    250.000

Upvotes: 1

Views: 1140

Answers (2)

user641605
user641605

Reputation: 421

This is what I ended up with that worked like a charm! Thanks Zohaib! 

UPDATE   t1
SET    t1.ocr = l1.ocr    
FROM    ( SELECT    *
              , rnk = ROW_NUMBER() OVER ( PARTITION BY t.customer,
                                          t.Amount ORDER BY t.customer, t.Amount )
           FROM      table t) t1
        LEFT JOIN 
    ( SELECT    *
              , rnk = ROW_NUMBER() OVER ( PARTITION BY l.customer,
                                          l.Amount ORDER BY l.date, l.id)
          FROM      lookuptable l) l1

      ON t1.id = l1.id
        AND t1.Amount = l1.amount
        AND t1.rnk = l1.rnk

Upvotes: 0

Zohaib
Zohaib

Reputation: 7116

update table set ocr = 

(select l.ocr
from 
(select l.customer as customer, l.ocr as ocr, l.amount as amount, l.date as date, ROW_NUMBER() OVER (partition by l.customer, l.amount Order BY l.date) as RowNum
from lookuptable l
order by l.date
)a

(select t.customer as customer, t.amount as amount, ROW_NUMBER() OVER (PARTITION BY t.customer, t.amount order by t.customer) as RowNum
from table t
)b
where a.customer = b.customer and a.amount=b.amount and a. rowNum = b.RowNum
)

I have not tested it, but its might give you an idea.

Edit: just realized no need to join in inner query. Idea is to first select all the records from look up table and assign them row number in ascending order of date. so same customer and same amount with different dates will get row numbers in increment orders.

Then get records from old table and assign row numbers to them partiioned by customer and amount. This way we can match customer, amount and row number sunch that first same customer and amount will be initialized oldest OCR, as rows are ordered by date

Upvotes: 1

Related Questions