Need to update 1 table from another

[Agro42]

I need to update a DB2 table with values from a temporary table, populated from a file.
The ASTU is my student record table, the EPS_CODES is my temp table. ASTU contains about 100 fields, the only one of which I need to update is named ENGPROF. The PK is PERMNUM.
EPS_CODES has 3 fields, SCHOOL, ID and EPSCODE.
I need to populate the ASTU.ENGPROF field with the value in EPS_CODES.EPSCODE where ASTU.SCHOOLNUM = EPS_CODES.SCHOOL and ASTU.PERMNUM = EPS_CODES.ID.
The SQL I tried is:
update astu3201
set engprof = (select epscode from eps_codes where schoolnum = school and permnum = id)

which returned an error message of "During SQL processing it returned: SQL0407N Assignment of a NULL value to a NOT NULL column "TBSPACEID=2, TABLEID=3686, COLNO=30" is not allowed. SQLSTATE=23502"

All fieldnames are unique within the tables referenced.
I'm stuck. Help please!

Andrew.

 

[MarcLodge]

Hi Andrew,
Have a look at the Table Definition for the EPS_CODES table and in particular the column EPSCODES and also the definition of the ENGPROF column in the ASTU3201 table.

From your error I would assume that the EPSCODES column has been defined to allow NULL values whereas the ENGPROF column hasn't. I'm guessing that you have a row on the EPSCODES table with a NULL value which is attempting to update the ASTU3201 table, which is not allowed.

Let me know how you get on.

Marc

 

[Agro42]

Nope. EPSCODE is defined as NOT NULL WITH DEFAULT.

Any other suggestions?

 

[MarcLodge]

Hmmm.... what number field is ENGPROF in your ASTU table?

 

[MarcLodge]

Another thought along similar lines to the first....
can any of the columns you are attempting to join on contain NULL values and their counterparts not? It could be that one of these on the temporary table can contain NULLS, but cannot on the ASTU. From the error message the column that is getting into trouble is the 30th column in the ASTU table. What is this column?

Marc

 

[Agro42]

I managed to get it to work. It was a wierdo. Apparently I needed to have a join both inside my subselect and outside the subselect. Without that, I was doing a non "correlated update".
My SQL wound up looking like:
update astu3201 set engprof =
(select epscode from eps_codes where schoolnum = school and permnum = id)
where permnum in (select id from eps_codes where school = '201')

Thanks for your suggestions Marc. I wanted to post the answer in case someone else is having for the same problem.

Andrew.