Hi I am trying to update the record after inserting the records, but the table has some NULLs. I am using DB2 UDB v8.2.

Here is the scenario:

Table has 4 columns, I need to first insert data and populate only 3 fields
once data inserted updated 4th field with value (first record from multiple records of other table).

I am using insert and then update. Here are the sqls..Is there any way to combine this into single sql or have any way to insert and update and not get null values.

Table: sch.CASE with fields - ID,STATUS,APPL_NO,cnameid

--insert

INSERT INTO sch.CASE (ID,STATUS,APPL_NO)
(SELECT DISTINCT ID,
CASE
WHEN STATUS='O' THEN 'CS4'
WHEN STATUS='P' THEN 'CS7'
WHEN STATUS='C' THEN 'CS3'
END AS STATUS,APPL_NO
FROM sch.sourcetable ORDER BY 1);

--update

UPDATE sch.CASE C1
SET CNAMEID = (SELECT DISTINCT C.ID FROM sch.CLIENT C WHERE C.ID=C1.ID
FETCH FIRST 1 ROWS ONLY);

Any pointer where it is going wrong?? Why I am getting NULL in sch.CASE table after update?? Is there any way to combine there 2 and get right result or alternate sqls.

Your UPDATE statement does not contain a WHERE clause (only the subselect does). Therefore, the UPDATE will operate on all rows of the table.

Once that is established, let's look at the subselect: this is a correlated subquery to find any row from table SCH.CLIENT with the same ID as the current row from SCH.CASE. If there is a matching row in SCH.CLIENT, you will get the C.ID value. If there is no matching row (remember, all rows of SCH.CASE are updated), you will get NULL as result of the scalar subselect.

What you probably want to have is this:
Notes:

• The DISTINCT in the subselect is unnecessary. You are doing a FETCH FIRST 1 ROW ONLY, so at most 1 row can be returned. If you have only 1 row, you cannot have duplicates. Hence, you don't need the duplicate elimination of DISTINCT.
• Don't worry about the two subqueries. DB2 will merge and execute them only once.
• You are setting SCH.CASE.CNAMEID to SCH.CLIENT.ID. But in the subselect, you check that this ID is the same as C1.ID. So do you need the subselect at all?
• The SQL MERGE statement may be another alternative.

ignore this .. I have opened another thread.