CODEWITHSUNDEEP
sqlsql-servert-sql
AAA
AAA

Reputation: 2450

SQL Server - Update statement with max and group by

I have a SQL Server table with columns like this but with only the Salary_ID column populated:

Name: Salary
Salary_ID, TotalAmount

I have another SQL Server table with this format and all the columns populated:

Name: SalaryImport
Row_ID, Salary_ID, Amount

I am trying to update the Salary table with the maximum row_id for each salary_id. Currently, there are many instances of each Salary_ID in SalaryImport and I only care about the most recent one which is indicated by the higher number row_id.

Any tips on how this can be done? I thought I would run a SQL statement like but it is grouping too much (and I can remove amount from the group because it is in the select!):

select max(Row_ID), Salary_ID, Amount
from SalaryImport e
group by  Row_ID, Amount

Upvotes: 2

Views: 2132

Answers (1)

Pரதீப்
Pரதீப்

Reputation: 93694

Use ROW_NUMBER window function

;with cte as
(
select row_number() over(partition by Salary_ID order by Row_ID desc) as rn,*
From yourtable
)
Update S
Set TotalAmount = c.Amount
From Salary s join cte c on s.Salary_ID = c.Salary_ID
Where RN = 1

Upvotes: 7

Related Questions