Update a table by selecting the max record

Question

Post reply

Update a table by selecting the max record

daryl.mclean

SSC Enthusiast

Points: 153
More actions
March 4, 2009 at 10:14 pm

#218857

I need to update a table to add 3 years to the expiry date of a course. One person could have several course names but different expiry dates
e.g.
Student No CourseNo Pass Date Expiry Date
112 124 01/01/2000 01/01/2002
112 124 01/01/2002 01/01/2004
112 124 01/01/2005 01/01/2007
How do i select the latest expiry date and add 3 years to the year
I can add the 3 years by using dateadd (yyyy, 3) but i cannot put this together inside a Select statement, any help greatly appreaciated

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Glenn Dorling SSCertifiable Points: 7876 More actions · Answer 1

You need to do something like this:

update Courses

set ExpDate = dateadd(year, 3, ExpDate)

from Courses a

where a.StudentId = 112

and a.CourseId = 124

and a.ExpDate = (select max(b.ExpDate)

from Courses b

where a.StudentId = b.StudentId

and a.CourseId = b.CourseId)

Under SQL7/2k there's not much alternative to sub-queries that I've found. Under SQL2k5 you can use a CTE (With clause) instead of the sub-query: for a single-row update it won't make any difference but for multiple rows in a large table it might make a huge difference.

ChiragNS One Orange Chip Points: 26137 More actions · Answer 2

[font="Verdana"][/font]You cant do the Select & Update in a single query.

Maybe you can write a small proc where you can do the update and then select the data.

[font="Arial"][font="Arial Narrow"][/font][/font]

"Keep Trying"

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 3

Chirag (3/4/2009)
You cant do the Select & Update in a single query.

Ummm... I'm pretty sure you can... see Glenn's post above. That's one of the reasons I love SQL Server's update... you can use FROM clauses with it.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ChiragNS One Orange Chip Points: 26137 More actions · Answer 4

I meant returning a set of records using a select and a update operation using the same query.

Seems i did'nt clearly understand the OP's question.

"Keep Trying"