Update 1 field out of 5

Transact-SQL
1

Hi

I have data as follows

[code]

ID Fundcode SecurityName MarketValue
1305357 261600 Deliver ARSReceive USD -666.37
1305424 261600 Deliver ARSReceive USD -1027.93
1324842 261600 Deliver ARSReceive USD 8893.12
1327421 261600 Deliver ARSReceive USD 1808.53
1332566 261600 Deliver ARSReceive USD -5247.47

[\code]

What i am looking to to is sum market value based on securityname and fundcode but only populated the summed value to on of the 5 outputs above.

I was trying the following but the group by is killing it

[code[
UPDATE m
SET m.[MarketValueofSharesSold]=f.MarketValue
FROM [dbo].[BNYUKWorkingDataloadFile2] m
INNER JOIN
(
SELECT [FundCode],SUM([MarketValue]) MarketValue ,ROW_NUMBER() over (Partition by [FundCode],[SecurityName] order by [FundCode],[SecurityName]) AS rowNumber
FROM [dbo].[BNYUKWorkingDataloadFile2]
GROUP BY [FundCode],[SecurityName]
) f ON m.[FundCode] = f.[FundCode] AND m.[SecurityName] = f.[SecurityName]
Where rowNumber = 1

[\code]

Anyone any ideas

2

you can first put the (group by part into a temp table)
so that the main bottleneck processing will be done quickly

then try the update
with the temp table in join

hope it helps
:slight_smile: :slight_smile:

1 Like
3

You don't need a row number when you've GROUPed by the key columns and done a SUM of the values. Simply do this:

UPDATE m
SET m.[MarketValueofSharesSold]=f.MarketValue
FROM [dbo].[BNYUKWorkingDataloadFile2] m
INNER JOIN
(
SELECT [FundCode],SUM([MarketValue]) MarketValue
FROM [dbo].[BNYUKWorkingDataloadFile2]
GROUP BY [FundCode], [MarketValue]
) f ON m.[FundCode] = f.[FundCode] AND m.[SecurityName] = f.[SecurityName]
1 Like