Re: How to capture MAX id value into a variable

On Wed, Nov 17, 2004 at 03:51:47PM -0800, Kumar S wrote:
>
> INSERT INTO EMPLOYEE(<columns>) VALUES(col1,col2,col3);
> SELECT INTO X = SELECT MAX(employee_id) from employee;
> INSERT INTO DUTY(<cols>) VALUES(X, col4,col4,col6);

If employee_id gets a default value from a sequence (e.g., if you
defined it as SERIAL) then you can use currval() to get the most
recently obtained value from the sequence.

INSERT INTO EMPLOYEE (...) VALUES (...);
INSERT INTO DUTY (...) VALUES (currval('employee_employee_id_seq'), ...);

currval() returns the most recently obtained value in the current
connection, so it's safe to use even if other connections are
updating the sequence at the same time.

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/