SQL syntax? [Select within Insert]

[EP]

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]

Re: SQL syntax? [Select within Insert]

[Aleksandar Bradaric]

Hi,

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

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

 or

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

I think this is your query:

INSERT INTO Extra_Credit(Student_ID, Points) SELECT MAX(Student_ID), '25' from Students


Take care,
Aleksandar


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

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]