Results 1 to 13 of 13
  1. #1
    Join Date
    Aug 2002
    Location
    Charlotte NC
    Posts
    665

    Unanswered: Update Statment with complex Where clause

    I have a complex update statment with a complex where clause. The code is as follows

    Code:
    update DB2ADMIN.DW_RKEM_EQUIP_ACTIVITY_TBL_C  j
    set j.act_Time_id = (select s.TIME_ID from DB2ADMIN.REF_TIME_LU_TBL s
    where j.acttime = s.time_value and j.act_time_id <> s.time_id);
    I am getting the following error:
    Code:
    update DB2ADMIN.DW_RKEM_EQUIP_ACTIVITY_TBL_C  j set j.act_Time_id = (select s.TIME_ID from DB2ADMIN.REF_TIME_LU_TBL s where j.acttime = s.time_value and j.act_time_id <> s.time_id)
    DB21034E  The command was processed as an SQL statement because it was not a 
    valid Command Line Processor command.  During SQL processing it returned:
    SQL0407N  Assignment of a NULL value to a NOT NULL column "TBSPACEID=25, 
    TABLEID=11, COLNO=1" is not allowed.  SQLSTATE=23502
    The odd thing about this is that if I run the follwoing code I get a return:
    Code:
    select count(*) from DB2ADMIN.REF_TIME_LU_TBL s, DB2ADMIN.DW_RKEM_EQUIP_ACTIVITY_TBL_C  j
    where j.acttime = s.time_value and j.act_time_id <> s.time_id
    I have checked the reference table for null values, and the base table cant have nulls because i am updating a key column. Dose anyone have any ideas what I am doing wrong?
    ------------
    And back to SQL Server....I always find my way home
    View my Linkedin profile

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Your original stmt updates all records in j.
    Therefore when the condition was not met, j.act_time_id was being set to null.

    Try this one:
    (Check for syntax errors)

    update DB2ADMIN.DW_RKEM_EQUIP_ACTIVITY_TBL_C j
    set j.act_Time_id = (select case
    when j.acttime = s.time_value and j.act_time_id <> s.time_id then
    s.TIME_ID
    else j.act_Time_id
    end)
    from DB2ADMIN.REF_TIME_LU_TBL s
    );

    Another approach will be -
    update DB2ADMIN.DW_RKEM_EQUIP_ACTIVITY_TBL_C j
    set j.act_Time_id = (select s.TIME_ID from DB2ADMIN.REF_TIME_LU_TBL s
    where j.acttime = s.time_value and j.act_time_id <> s.time_id)
    where <preidcate>
    ;


    The predicate when applied should exclude all rows in j, that do not meet 'j.acttime = s.time_value and j.act_time_id <> s.time_id'

    HTH

    Sathyaram




    Quote Originally Posted by JDionne
    I have a complex update statment with a complex where clause. The code is as follows



    Code:
    update DB2ADMIN.DW_RKEM_EQUIP_ACTIVITY_TBL_C  j
    set j.act_Time_id = (select s.TIME_ID from DB2ADMIN.REF_TIME_LU_TBL s
    where j.acttime = s.time_value and j.act_time_id <> s.time_id);
    I am getting the following error:
    Code:
    update DB2ADMIN.DW_RKEM_EQUIP_ACTIVITY_TBL_C  j set j.act_Time_id = (select s.TIME_ID from DB2ADMIN.REF_TIME_LU_TBL s where j.acttime = s.time_value and j.act_time_id <> s.time_id)
    DB21034E  The command was processed as an SQL statement because it was not a 
    valid Command Line Processor command.  During SQL processing it returned:
    SQL0407N  Assignment of a NULL value to a NOT NULL column "TBSPACEID=25, 
    TABLEID=11, COLNO=1" is not allowed.  SQLSTATE=23502
    The odd thing about this is that if I run the follwoing code I get a return:
    Code:
    select count(*) from DB2ADMIN.REF_TIME_LU_TBL s, DB2ADMIN.DW_RKEM_EQUIP_ACTIVITY_TBL_C  j
    where j.acttime = s.time_value and j.act_time_id <> s.time_id
    I have checked the reference table for null values, and the base table cant have nulls because i am updating a key column. Dose anyone have any ideas what I am doing wrong?
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  3. #3
    Join Date
    Aug 2002
    Location
    Charlotte NC
    Posts
    665
    I cant do the following
    Code:
    Another approach will be - 
    update DB2ADMIN.DW_RKEM_EQUIP_ACTIVITY_TBL_C j
    set j.act_Time_id = (select s.TIME_ID from DB2ADMIN.REF_TIME_LU_TBL s
    where j.acttime = s.time_value and j.act_time_id <> s.time_id) 
    where <preidcate>
    ;
    because my predicate is comparing a table that is not specified. In SQL Server i can use a from cluse in an update statment...I dont think that is posable in DB2.

    the followign would work except I am still touching every row. there are 35 Million rows....that fills up my transaction logs

    Code:
    update DB2ADMIN.DW_RKEM_EQUIP_ACTIVITY_TBL_C j
    set j.act_Time_id = (select case when j.acttime = s.time_value and j.act_time_id <> s.time_id then s.TIME_ID else j.act_Time_id end) from DB2ADMIN.REF_TIME_LU_TBL s );
    any other thoughts?

    Quote Originally Posted by sathyaram_s
    Your original stmt updates all records in j.
    Therefore when the condition was not met, j.act_time_id was being set to null.

    Try this one:
    (Check for syntax errors)

    update DB2ADMIN.DW_RKEM_EQUIP_ACTIVITY_TBL_C j
    set j.act_Time_id = (select case
    when j.acttime = s.time_value and j.act_time_id <> s.time_id then
    s.TIME_ID
    else j.act_Time_id
    end)
    from DB2ADMIN.REF_TIME_LU_TBL s
    );

    Another approach will be -
    update DB2ADMIN.DW_RKEM_EQUIP_ACTIVITY_TBL_C j
    set j.act_Time_id = (select s.TIME_ID from DB2ADMIN.REF_TIME_LU_TBL s
    where j.acttime = s.time_value and j.act_time_id <> s.time_id)
    where <preidcate>
    ;


    The predicate when applied should exclude all rows in j, that do not meet 'j.acttime = s.time_value and j.act_time_id <> s.time_id'

    HTH

    Sathyaram
    ------------
    And back to SQL Server....I always find my way home
    View my Linkedin profile

  4. #4
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    You should find an example in a older(may 04) thread ..
    I have updated it today

    http://www.dbforums.com/showthread.php?t=999150

    Cheers
    Sathyaram

    Quote Originally Posted by JDionne
    I cant do the following
    Code:
    Another approach will be - 
    update DB2ADMIN.DW_RKEM_EQUIP_ACTIVITY_TBL_C j
    set j.act_Time_id = (select s.TIME_ID from DB2ADMIN.REF_TIME_LU_TBL s
    where j.acttime = s.time_value and j.act_time_id <> s.time_id) 
    where <preidcate>
    ;
    because my predicate is comparing a table that is not specified. In SQL Server i can use a from cluse in an update statment...I dont think that is posable in DB2.

    the followign would work except I am still touching every row. there are 35 Million rows....that fills up my transaction logs

    Code:
    update DB2ADMIN.DW_RKEM_EQUIP_ACTIVITY_TBL_C j
    set j.act_Time_id = (select case when j.acttime = s.time_value and j.act_time_id <> s.time_id then s.TIME_ID else j.act_Time_id end) from DB2ADMIN.REF_TIME_LU_TBL s );
    any other thoughts?
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  5. #5
    Join Date
    Aug 2002
    Location
    Charlotte NC
    Posts
    665
    Ya I saw that. obviously this has been an issue for me in one form or another. Unfortuanatly the updated thread does not address my issue of needing to qualify how many rows to update. I am working on a process that creates a lookup table containing only the info that I need to update. I dont think this will cause me many problems. Any other thoughts would be greatly aprecieated.
    Thanks
    Jim
    ------------
    And back to SQL Server....I always find my way home
    View my Linkedin profile

  6. #6
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Try this :


    update DB2ADMIN.DW_RKEM_EQUIP_ACTIVITY_TBL_C j
    set j.act_Time_id = (select s.TIME_ID from DB2ADMIN.REF_TIME_LU_TBL s
    where j.acttime = s.time_value and j.act_time_id <> s.time_id)

    where exists (select 1 from DB2ADMIN.REF_TIME_LU_TBL s1 where j.acttime = s1.time_value and j.act_time_id <> s1.time_id)


    Are you sure there are no nulls in s.TIME_ID ?
    Cheers
    Sathyaram



    Quote Originally Posted by JDionne
    Ya I saw that. obviously this has been an issue for me in one form or another. Unfortuanatly the updated thread does not address my issue of needing to qualify how many rows to update. I am working on a process that creates a lookup table containing only the info that I need to update. I dont think this will cause me many problems. Any other thoughts would be greatly aprecieated.
    Thanks
    Jim
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  7. #7
    Join Date
    Aug 2002
    Location
    Charlotte NC
    Posts
    665
    Ya im sure its the key to the table
    ------------
    And back to SQL Server....I always find my way home
    View my Linkedin profile

  8. #8
    Join Date
    Aug 2002
    Location
    Charlotte NC
    Posts
    665
    Ok I decided to go a different way because i thought that the problem was my complicated where. I put all the rows that I wanted to fix in a table with the corrected values and now I need to update the original table. This is my code:

    Code:
    update DB2ADMIN.DW_RKEM_EQUIP_ACTIVITY_TBL_C  j
    set j.act_Time_id = (select s.COR_ACT_TIME_ID from DB2ADMIN.REF_RKEM_TIME_VALUES_UPDATE_LU_TBL s
    where j.EQPNO = s.EQPNO and j.acttime = s.acttime and j.actdate = s.actdate 
    	and exists (select 1 from DB2ADMIN.REF_RKEM_TIME_VALUES_UPDATE_LU_TBL where j.EQPNO = s.EQPNO and j.acttime = s.acttime and j.actdate = s.actdate)) ;

    I am still getting the same error
    Code:
    DB21034E  The command was processed as an SQL statement because it was not a 
    valid Command Line Processor command.  During SQL processing it returned:
    SQL0407N  Assignment of a NULL value to a NOT NULL column "TBSPACEID=25, 
    TABLEID=11, COLNO=1" is not allowed.  SQLSTATE=23502
    I am at a loss. any thoughts?
    Jim
    ------------
    And back to SQL Server....I always find my way home
    View my Linkedin profile

  9. #9
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    update DB2ADMIN.DW_RKEM_EQUIP_ACTIVITY_TBL_C j
    set j.act_Time_id = (select s.COR_ACT_TIME_ID from DB2ADMIN.REF_RKEM_TIME_VALUES_UPDATE_LU_TBL s
    where j.EQPNO = s.EQPNO and j.acttime = s.acttime and j.actdate = s.actdate )
    where exists (select 1 from DB2ADMIN.REF_RKEM_TIME_VALUES_UPDATE_LU_TBL s1 where j.EQPNO = s1.EQPNO and j.acttime = s1.acttime and j.actdate = s1.actdate) ;

    is the right way to do , to the best of my knowledge.



    Quote Originally Posted by JDionne
    Ok I decided to go a different way because i thought that the problem was my complicated where. I put all the rows that I wanted to fix in a table with the corrected values and now I need to update the original table. This is my code:

    Code:
    update DB2ADMIN.DW_RKEM_EQUIP_ACTIVITY_TBL_C  j
    set j.act_Time_id = (select s.COR_ACT_TIME_ID from DB2ADMIN.REF_RKEM_TIME_VALUES_UPDATE_LU_TBL s
    where j.EQPNO = s.EQPNO and j.acttime = s.acttime and j.actdate = s.actdate 
        and exists (select 1 from DB2ADMIN.REF_RKEM_TIME_VALUES_UPDATE_LU_TBL where j.EQPNO = s.EQPNO and j.acttime = s.acttime and j.actdate = s.actdate)) ;

    I am still getting the same error
    Code:
    DB21034E  The command was processed as an SQL statement because it was not a 
    valid Command Line Processor command.  During SQL processing it returned:
    SQL0407N  Assignment of a NULL value to a NOT NULL column "TBSPACEID=25, 
    TABLEID=11, COLNO=1" is not allowed.  SQLSTATE=23502
    I am at a loss. any thoughts?
    Jim
    Last edited by sathyaram_s; 01-19-06 at 15:57. Reason: corrected my typos in the stmt
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  10. #10
    Join Date
    Aug 2002
    Location
    Charlotte NC
    Posts
    665
    test test test
    Last edited by sathyaram_s; 01-19-06 at 16:01.
    ------------
    And back to SQL Server....I always find my way home
    View my Linkedin profile

  11. #11
    Join Date
    Aug 2002
    Location
    Charlotte NC
    Posts
    665
    THANKS FOR ALL THE HELP!!!!
    my final code
    Code:
    update DB2ADMIN.DW_RKEM_EQUIP_ACTIVITY_TBL_C j
    set j.act_Time_id = (select s.COR_ACT_TIME_ID from DB2ADMIN.REF_RKEM_TIME_VALUES_UPDATE_LU_TBL s
    where j.EQPNO = s.EQPNO and j.acttime = s.acttime and j.actdate = s.actdate )
    ,j.act_date_id = (select s.COR_ACT_date_ID from DB2ADMIN.REF_RKEM_TIME_VALUES_UPDATE_LU_TBL s
    where j.EQPNO = s.EQPNO and j.acttime = s.acttime and j.actdate = s.actdate )
    where exists (select 1 from DB2ADMIN.REF_RKEM_TIME_VALUES_UPDATE_LU_TBL s where j.EQPNO = s.EQPNO and j.acttime = s.acttime and j.actdate = s.actdate) ;
    ------------
    And back to SQL Server....I always find my way home
    View my Linkedin profile

  12. #12
    Join Date
    Aug 2002
    Location
    Charlotte NC
    Posts
    665
    Now I am totaly confused. I modified working code...and now it does not work!!!

    Code:
    update DB2ADMIN.DW_RKEM_EQUIP_ACTIVITY_TBL_C j
    set j.MOVE_Time_id = (select s.COR_MOVE_TIME_ID from DB2ADMIN.REF_RKEM_TIME_VALUES_UPDATE_LU_TBL s
    where j.EQPNO = s.EQPNO and time(j.MOVEDATETIME) = time(S.MOVEDATETIME) and date(j.MOVEDATETIME) = date(s.MOVEDATETIME))
    ,j.MOVE_date_id = (select s.COR_MOVE_date_ID from DB2ADMIN.REF_RKEM_TIME_VALUES_UPDATE_LU_TBL s
    where j.EQPNO = s.EQPNO and time(j.MOVEDATETIME) = time(S.MOVEDATETIME) and date(j.MOVEDATETIME) = date(s.MOVEDATETIME))
    where exists (select 1 from DB2ADMIN.REF_RKEM_TIME_VALUES_UPDATE_LU_TBL s where j.EQPNO = s.EQPNO and time(j.MOVEDATETIME) = time(S.MOVEDATETIME) and date(j.MOVEDATETIME) = date(s.MOVEDATETIME));
    Im sure its simple, but im just not seeing it.
    ------------
    And back to SQL Server....I always find my way home
    View my Linkedin profile

  13. #13
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    I think the following code will be more efficient:
    Code:
    update DB2ADMIN.DW_RKEM_EQUIP_ACTIVITY_TBL_C j
    set (j.act_Time_id,j.act_date_id) = 
    		(select s.COR_ACT_TIME_ID, s.COR_ACT_date_ID 
    		from DB2ADMIN.REF_RKEM_TIME_VALUES_UPDATE_LU_TBL s
    		where j.EQPNO = s.EQPNO and 
    			j.acttime = s.acttime and 
    			j.actdate = s.actdate )
    where exists (select 1 
    		from DB2ADMIN.REF_RKEM_TIME_VALUES_UPDATE_LU_TBL s 
    		where j.EQPNO = s.EQPNO and 
    			j.acttime = s.acttime and 
    			j.actdate = s.actdate) ;
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

Posting Permissions

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