473,322 Members | 1,409 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

update field with max from another table

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
Jul 20 '05 #1
2 15126
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
Jul 20 '05 #2
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)
Jul 20 '05 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
by: Ike | last post by:
Is there a way in mysql to discern when the last date/time a field, row, or table was been updated or inserted? Thanks, ike
2
by: Mike Leahy | last post by:
Hello all, This question is related to updating tables - is there any way to calculate or update the values in a column in a table to the values in a field produced by a query result? An...
3
by: rrh | last post by:
I am trying to update a field in one table with data from another table. The problem I'm running into is I need to base the update on a range of data in the 2nd table. Table 1 has: date field...
4
by: N. Graves | last post by:
Hello... thank you for your time. I have a form that has a List box of equipotent records and a sub form that will show the data of the equipment select from the list box. Is it possible to...
8
by: Maxi | last post by:
There is a lotto system which picks 21 numbers every day out of 80 numbers. I have a table (name:Lotto) with 22 fields (name:Date,P1,P2....P21) Here is the structure and sample data: ...
15
by: Darren | last post by:
Help, i want to run an update query from a form.. and was wonderin.. Can the update query run if i want to update a value manually inputted from a form (e.g. !!) to a table...
2
by: Miro | last post by:
I will ask the question first then fumble thru trying to explain myself so i dont waste too much of your time. Question / Statement - Every mdb table needs a PrimaryKey ( or maybe an index - i...
13
by: shookim | last post by:
I don't care how one suggests I do it, but I've been searching for days on how to implement this concept. I'm trying to use some kind of grid control (doesn't have to be a grid control, whatever...
2
by: joeyrhyulz | last post by:
Hi, I'm trying to make a very simple update statement (in Oracle) in jet sql that seems much more difficult than it should be. The root of my problem is that I'm trying to update a field on a...
4
by: hapnendad | last post by:
In the question statement below Field names are in and variables are in (). All fields referenced are in what I have named the ‘PAR’ Table. Using MS Access 2003, I am working on a project...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.