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?

+1 I suggest you change the title of the question to something like "Inserting a row from sub-select - NULL error", and add "SQL" tag, since this problem is generic and not DB2-specific. — Roee Adler

Roee Adler Roee Adler · Accepted Answer · 2009-05-17T05:12:49

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?

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

1 Answers