CODEWITHSUNDEEP
sqlsql-server
Pat
Pat

Reputation: 310

Update multiple rows with max date for each foreign key

I can select the information for the row(s) I want to update using this statement:

select max(Date),F_ID from MyTable group by F_ID

F_ID is a foreign key. I'm trying to update a different field where the date is at the maximum. The issue is that there may be many rows with the same foreign key and the same date. In the case that these are the max date has multiple rows I want to update all of them.

The closest I've been able to get is this:

update MyTable set
Curr = 1
where (select cast(Date as varchar(20))+cast(F_ID as varchar(10)) from MyTable) in
(select cast(max(Date)as varchar(20))+cast(F_ID as varchar(10)) from MyTable group by A_PID)

I get this error:

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.

Thanks for the help!

Edit: Final query format for the update statement in case someone else is looking for this:

update MyTable set
    Curr = 1
from MyTable t inner join
    (select F_ID,max(Date) maxDate
     from MyTable group by A_PID) t2
on t.A_PID = t2.A_PID and t.Date = t2.maxDate

Thanks to @Jim

Upvotes: 2

Views: 2486

Answers (1)

LINQ2Vodka
LINQ2Vodka

Reputation: 3036

group rows by FK, select FK and Max(date) as MaxDate, then join result to the same table on FK=FK and date=maxDate (hope you dont have equal dates per FK)
If dates really equal per FK then take a look to another my answer 

SELECT t1.* FROM Table t1
INNER JOIN 
    (SELECT F_ID, max(date) maxDate 
     FROM Table 
     GROUP BY F_ID) t2 
ON t1.F_ID=t2.F_ID AND t1.date=t2.maxDate

Upvotes: 3

Related Questions