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:<\/p>\n
\nCREATE TABLE temps (\n id INT NOT NULL,\n name VARCHAR(10) NOT NULL,\n PRIMARY KEY (id)\n);\n<\/pre>\nI thought could create a single insert statement that also managed to auto increment the id primary key. I thought I could do something like:<\/p>\n
\nINSERT INTO temps VALUES(\n (SELECT max(t.id)+1 FROM\n (SELECT id FROM temps UNION SELECT 0)\n t),\n'juixe');\n<\/pre>\nWhen 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.”<\/p>\n
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.<\/p>\n
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:<\/p>\n
\nINSERT INTO temps VALUES(\n (SELECT max(t.id)+1 FROM\n (SELECT id FROM temps UNION SELECT 0 from user_tables)\n t),\n'techknow');\n<\/pre>\nWhat 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.<\/p>\n
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:<\/p>\n
\nSELECT id FROM temps UNION SELECT 0;\n<\/pre>\nAdding 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.<\/p>\n
\nINSERT INTO temps VALUES(\n (SELECT max(t.id)+1 FROM\n (SELECT id FROM temps UNION SELECT 0)\n t),\n'zenze');\n<\/pre>\nSo 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.<\/p>\n
Technorati Tags: sql<\/a>, query<\/a>, subqueries<\/a>, mysql<\/a>, sql server<\/a>, oracle<\/a>, postgresql<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"
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 […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"spay_email":"","footnotes":""},"categories":[23,3],"tags":[],"jetpack_featured_media_url":"","jetpack_shortlink":"https:\/\/wp.me\/p902K-1S","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"http:\/\/juixe.com\/techknow\/index.php\/wp-json\/wp\/v2\/posts\/116"}],"collection":[{"href":"http:\/\/juixe.com\/techknow\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/juixe.com\/techknow\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/juixe.com\/techknow\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/juixe.com\/techknow\/index.php\/wp-json\/wp\/v2\/comments?post=116"}],"version-history":[{"count":0,"href":"http:\/\/juixe.com\/techknow\/index.php\/wp-json\/wp\/v2\/posts\/116\/revisions"}],"wp:attachment":[{"href":"http:\/\/juixe.com\/techknow\/index.php\/wp-json\/wp\/v2\/media?parent=116"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/juixe.com\/techknow\/index.php\/wp-json\/wp\/v2\/categories?post=116"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/juixe.com\/techknow\/index.php\/wp-json\/wp\/v2\/tags?post=116"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}