Inserting a row into DB2 from a sub-select - NULL error

Question

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?

Answer 1

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?