Re: SQL syntax? [Select within Insert]

[Michael Stassen]

As I understand it, you don't really want the MAX(Student_ID), you want 
the actual Student_ID of the last insert.  It is important to note that 
they are not necessarily the same.  If you insert Student 24, then I 
insert Student 25, then you check MAX(Student_ID), you will get 25, not 
24.  Hence, you'll end up using the wrong value.  Also, some table types 
 will reuse IDs from deleted rows.

Fortunately, mysql provides a solution.  The LAST_INSERT_ID() function 
returns the most recent AUTO_INCREMENT value.  It is also 
connection-specific, so it is not affected by what someone else is 
doing.  So, your second statement should be

  INSERT INTO Extra_Credit (Student_ID, Points)
  VALUES (LAST_INSERT_ID(), 25)
Michael

EP wrote:

Struggling to get an INSERT to work, can anyone help?

Here's my scenario:

Students    [table]
------------
Student_ID    [primary key, auto-increment]
Student_name
Student_sex
Extra_Credit    [table]
------------
EC_ID     [primary key, auto-increment]
Student_ID
Points
First:  INSERT INTO Students (Student_name, Student_sex) VALUES('"Josh 
Baxter", "M");

[suceeds]

Then:  INSERT INTO Extra_Credit (Student_ID, Points) SELECT 
MAX(Student_ID) from Students,

(1)    ...VALUE ('25');

or

(2)    ... '25' as Points;

Either one fails... Any hints on syntax to achieve the insert (pulling 
the Student_ID in from the just modified record in the Students table)?

TIA!

Eric Pederson







--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]