Hi!

I've read some about this but I'm not sure weather or not I really understood it yet. I want to make a new insert, but one of the fields should be data selected from the same table.
I thought I could do this (data is irrelevant, just testing still)


INSERT INTO litters(litterNumber,father,mother,birthDate) VALUES((SELECT MAX(litterNumber) FROM litters),'46','47','2009-01-31')

Is this possible to do somehow?
If so, can I also select MAX(litterNumber)+1 since I want the new entry to have it's litterNumber increased by one?

Thanks
lizciz

EDIT: Oh, and if you want to see the error message, here it isYou can't specify target table 'litters' for update in FROM clause

It works, but the query must be adapted to:

INSERT INTO litters(
  litterNumber,
  father,
  mother,
  birthDate
) 
SELECT
  MAX(litterNumber)+1
  ,46
  ,47
  ,'2009-01-31'
FROM litters

I know it's working in ms sql server, and postgresql. And I've just tested it in mysql 5.

Indeed, it works great. Thank you!