Menu
I am trying to insert a row into a table, using a value that is derived from another table. Here is the SQL statement that I am trying to use:
INSERT INTO NextKeyValue(KeyName, KeyValue) SELECT 'DisplayWorkItemId' AS KeyName, (MAX(work_item_display_id) + 1) AS KeyValue FROM work_item;
So, I am trying to create a row in NextKeyValue that has 'KeyName' of 'DisplayWorkItemId' and 'KeyValue' of one more than the maximum value in work_item.work_item_display_id.
The SELECT statement in the above query returns the expected result, when I run it on its own.
The whole SQL query is giving me the following error, though:
Error: DB2 SQL Error: SQLCODE=-407, SQLSTATE=23502, SQLERRMC=TBSPACEID=2, TABLEID=75, COLNO=2, DRIVER=3.50.152 SQLState: 23502 ErrorCode: -407
What does this mean, and what is wrong with my query?
852
You can use a select-statement within an INSERT statement to insert zero, one, or more rows into a table from the result table of the select-statement. The select-statement embedded in the INSERT statement is no different from the select-statement you use to retrieve data.
Introduction to Db2 INSERT INTO SELECT statement First, specify the name of the target table to which the rows will be inserted and a list of columns. Second, use a SELECT statement to query data from another table. The SELECT statement can be any valid query that returns zero or more rows.
Db2 INSERT multiple rows statement overview To insert multiple rows into a table, you need to: First, specify the name of the table and a list of columns in parentheses. Second, use a list of comma-separated lists of column values. Each item in the list represents a row that will be inserted into the table.
The most probable explanation is that you have additional columns in NextKeyValue table that can't accept NULL values, and this INSERT statement is "trying" to put NULL in them.
Is that the case by any chance?
152
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
