Update records based on a fields Max value

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Update records based on a fields Max value

Author

Topic

estacyd
Starting Member

2 Posts

Posted - 2005-07-28 : 15:29:30

I have a table EEEnroll that has 4 fields:
EmpUID, PlanID, HistSeqNbr,StartDate

There are multiple records with the same EmpUID and PlanId but different HistSeqNbr (increment sequentially)

I want to update the StartDate with a value ONLY if the HistSeqNbr is the highest value of the records where EmpUID and PlanId are the same.

Here is my SQL. It seems to update ALL the records:

update EEEnroll
set startdate='8/1/2005'
WHERE exists
(select e.empuid,max(e.histseqnbr)
from EEENroll e
WHERE e.companyid=5
AND (e.empuid=EEENROLL.empuid and e.planid=EEENroll.planid and e.histseqnbr=EEENroll.histseqnbr)
GROUP BY e.empuid)

jhermiz

3564 Posts

Posted - 2005-07-28 : 15:50:23

you need to add a WHERE to your UPDATE clause. That is why it is updating all of the records. Look carefully at it. You have a where in the select but not the update.

Jon

Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]
Imperfection living for perfection -- [url]http://jhermiz.blogspot.com/[/url]

estacyd
Starting Member

2 Posts

Posted - 2005-07-28 : 17:15:04

Think I have it:

update a
set startdate='8/1/2005'
from EEENROLL a
JOIN
(select max(e.histseqnbr) as MaxHist,e.empuid,e.planid
FROM EEEnroll e
WHERE e.companyid=5
GROUP BY e.empuid,e.planid
) subquery ON
subquery.empuid=a.empuid and subquery.MaxHist=a.histseqnbr and subquery.planid=a.planid

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources