Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2007
    Posts
    21

    Unanswered: Null while updating records

    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.

  2. #2
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    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:
    Code:
    UPDATE sch.case AS c1
    SET cnameid = ( SELECT c.id
                    FROM   sch.client AS c
                    WHERE  c.id = c1.id
                    FETCH FIRST 1 ROWS ONLY )
    WHERE EXISTS ( SELECT c.id
                   FROM   sch.client AS c
                   WHERE  c.id = c1.id
                   FETCH FIRST 1 ROWS ONLY )
    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.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  3. #3
    Join Date
    Jun 2007
    Posts
    21

    Error: DB2 SQL error: SQLCODE: -955, SQLSTATE:, SQLERRMC: 2

    ignore this .. I have opened another thread.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •