INSERT_ID

[xpherism]

Hi...
Found at lot of post discussing LAST_INSERT_ID, but none of the seems to be able to help me.

I would like make query looking like this:
"INSERT INTO node (thread) VALUES (id)".

"id" is an auto_increment column, and I would like to automatically set the value of "thread" to same value as the "id" column, during an insert statement.

LAST_INSERTED_ID() won't work (I should should return a value seen from a global perspective, per connection). And you cannot fo something like

"INSERT INTO node (thread) SELECT MAX(LAST_INSERT_ID(id+1)) from node" as it is an illegal statement (as you cannot have the same table as the target table and in the FROM clause, due to some ANSI SQL restictions.) :-(

Any good suggestion?????????

[[deleted]]

I don't think there is a taboo on inserting into a table from the table itself in ANSI sql. At least, postgres doesn't mind.

You mention using auto_increment, which is a mysql thing. Are you using mysql?

Anyway, the solution is so simple it hurts: run two queries, one to get the value, and one to insert it.

And a final note: NEVER EVER use things like 'MAX(auto_increment_field)' because that will NOT get you the 'last_insert_id' for your connection, it will get the highest available id for that column.

[xpherism]

Yes you're right. I forgot to mention that I use mysql. And the thing about ANSI SQL restriction, is mentioned in MySQL manual. :-)

I know that I could use two queries, but would really like to do the same in only "one" query! (Should also have pointed that out)

So if there are any other suggestion, please drop a word.

Thanx.

[[deleted]]

I think the main 'restriction' you are experiencing is the fact that MySQL does not support subqueries. :-)

Anyway, you can't get the insert_id untill the row is inserted, so you'll have to make two queries no matter what.

If MySQL was a halfway decent database, you'd be able to place a trigger inside the database that could update the record immediately after it was inserted.

[xpherism]

Thanks!

guess I either use two calls or find myself another DB. :-)

ps. I hate the fact, that Mysql doesn't support embedded select. Any good free DB around that support trigger and embedded select?

[[deleted]]

'embedded select' is called 'subqueries'.

Try Postgres, it's free and loaded with features. Plus it's a REAL Rdbms, so it supports foreign-key constraints.