Update by rank using subquery

• philip_egan

  SSC Journeyman

  Points: 97

  More actions

  October 25, 2013 at 4:08 am

  #278512

  Hi folks

  Hope you can help, and I've put this in the right section.

  My predicament is as follows -

  I have a table of outstanding invoices which I've ranked in order of date, all fine there.

  I have another table containing payments and I want to assign each individual payment to an individual invoice using an UPDATE statement.

  I was hoping to join on the ranks of each table and do something like following -

  UPDATE @Temp_Allocations

  SET Payment =

  (SELECT Gross

  FROM [Stage].[S_XLS_Allocation] alloc

  AND

  (Gross = OutstandingAmount

  OR

  OutstandingAmount - Gross BETWEEN 0 AND 2)

  AND RANK() OVER (PARTITION BY Asset,Serial ORDER BY PaymentDate) = RankSequence

  AND IndividualItemID = alloc.Asset

  )

  But this gives me the following error -

  Windowed functions can only appear in the SELECT or ORDER BY clauses

  Can anyone suggest another way of doing this in a single hit UPDATE without having to loop through the table variable?

  Thanks in advance

  Phil

• Nevyn

  SSChampion

  Points: 14210

  More actions

  October 25, 2013 at 6:47 am

  #1661608

  Can you post table definitions and some sample data?

• philip_egan

  SSC Journeyman

  Points: 97

  More actions

  October 25, 2013 at 6:53 am

  #1661611

  I think I've sorted now thank you - Just finalising and I'll post details but I'm putting the payments and their row numbers in a second temp table and then using an update to marry up the temp invoices and temp payments