update field with max from another table

[R Camarda]

I have two tables STUDENTS and REGISTRATIONS. I wish to update my
STUDENT table field, LAST_ATTENDED_SESSION_ID from my REGISTRATION
table of the max SESSION_ID found in the REGISTRATION table.

STUDENT starts off with:
STUDENT_ID LAST_ATTENDED_SESSION_ID
123456789 000000
234567890 000000
345678901 000000

REGISTRATIONS:
STUDENT_ID SESSION_ID CLASS_ID
123456789 200001 BUS100
123456789 200102 BUS150
123456789 200203 BUS160

I want to update my STUDENT.LAST_ATTENDED_SESSION_ID with the
MAX(SESSION_ID) found in REGISTRATIONS for each student.
I'd end up with:
STUDENT_ID LAST_ATTENDED_SESSION_ID
123456789 200203
234567890 000000
345678901 000000
I've found examples here about selects and sub-queries, but nothing
yet in my situation with an update.
TIA
Rob

[Martin]

Hi.

Maybe this could work:

update students
set last_attended_session_id = (select max(session_id)
from students as s
inner join registrations as r on
r.student_id = s.student_id
and students.student_id = s.student_id
)
Martin

[Hugo Kornelis]

On 15 Apr 2004 06:10:43 -0700, R Camarda wrote:

I have two tables STUDENTS and REGISTRATIONS. I wish to update my
STUDENT table field, LAST_ATTENDED_SESSION_ID from my REGISTRATION
table of the max SESSION_ID found in the REGISTRATION table.

STUDENT starts off with:
STUDENT_ID LAST_ATTENDED_SESSION_ID
123456789 000000
234567890 000000
345678901 000000

REGISTRATIONS:
STUDENT_ID SESSION_ID CLASS_ID
123456789 200001 BUS100
123456789 200102 BUS150
123456789 200203 BUS160

I want to update my STUDENT.LAST_ATTENDED_SESSION_ID with the
MAX(SESSION_ID) found in REGISTRATIONS for each student.
I'd end up with:
STUDENT_ID LAST_ATTENDED_SESSION_ID
123456789 200203
234567890 000000
345678901 000000
I've found examples here about selects and sub-queries, but nothing
yet in my situation with an update.
TIA
Rob

Hi Rob,

Without any DDL, I can do no more than take a wild guess at your
actual table structure, keys, constraints etc.

Maybe the following works:

UPDATE Students
SET Last_Attended_Session_ID =
(SELECT MAX(Session_ID)
FROM Registrations
WHERE Registrations.Student_ID = Students.Student_ID)
WHERE EXISTS
(SELECT *
FROM Registrations
WHERE Registrations.Student_ID = Students.Student_ID)
Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)