Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Update by rank using subquery


Update by rank using subquery

Author
Message
philip_egan
philip_egan
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 74
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
Nevyn
SSC Eights!
SSC Eights! (960 reputation)SSC Eights! (960 reputation)SSC Eights! (960 reputation)SSC Eights! (960 reputation)SSC Eights! (960 reputation)SSC Eights! (960 reputation)SSC Eights! (960 reputation)SSC Eights! (960 reputation)

Group: General Forum Members
Points: 960 Visits: 3149
Can you post table definitions and some sample data?
philip_egan
philip_egan
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 74
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search