Query Managed Auto Increment
Different database vendors allow you to auto increment an integer primary key in different ways. I thought that I could manage to increment the primary key from within the same insert statement. Given the following table:
CREATE TABLE temps ( id INT NOT NULL, name VARCHAR(10) NOT NULL, PRIMARY KEY (id) );
I thought could create a single insert statement that also managed to auto increment the id primary key. I thought I could do something like:
INSERT INTO temps VALUES( (SELECT max(t.id)+1 FROM (SELECT id FROM temps UNION SELECT 0) t), 'juixe');
When I run the above insert statement using MySQL 4.1.15 I get an error that reads, “You can’t specify target table ‘temps’ for update in FROM clause.”
Using Microsoft SQL Server 2005 Express I get an error that reads, “Subqueries are not allowed in this context. Only scalar expressions are allowed.” This is most unfortunate because subqueries are really powerful.
I had better luck on Oracle Database 10g Express Edition where I could run what I call Query Managed Auto Increment statement. On Oracle 10g EX I was able to run the following Query Managed Auto Increment statement with a minor modification:
INSERT INTO temps VALUES( (SELECT max(t.id)+1 FROM (SELECT id FROM temps UNION SELECT 0 from user_tables) t), 'techknow');
What doesn’t work well in Oracle is that you can’t run a select statement without a table name. On Oracle you can’t run the following statement: select 0. Query Managed Auto Increment works in Oracle but you would need default to a table that has at least one record, such as the user_tables table.
Just for completeness I tried PostgreSQL 8.1.4. PostgreSQL supports static select statements, select statements without the from clause and return a constant value. Because PostgreSQL supports static select statements I am guaranteed that the following will return at least one row of ids even if the table is empty:
SELECT id FROM temps UNION SELECT 0;
Adding one to the max id returns the next primary key id, which I use in the insert statement. Once again here is the working SQL (in PostgreSQL) that manages the primary key and automatically increments the id to the next available integer value.
INSERT INTO temps VALUES( (SELECT max(t.id)+1 FROM (SELECT id FROM temps UNION SELECT 0) t), 'zenze');
So in conclusion, Query Managed Auto Increment doesn’t exactly work in MySQL because you can’t use a subquery that refers the same table as the insert. SQL Server has the limitation that you can’t even execute a subquery in an insert. And as we have seen Query Managed Auto Increment works on Oracle and PostgreSQL.