Update Statment with complex Where clause

JDionne · #1 (**permalink**) 01-18-06, 11:55

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?

sathyaram_s · #2 (**permalink**) 01-18-06, 12:52

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?

JDionne · #3 (**permalink**) 01-18-06, 13:08

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 jset 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

sathyaram_s · #4 (**permalink**) 01-18-06, 13:36

You should find an example in a older(may 04) thread ..
I have updated it today

Update Statment

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 jset 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?

JDionne · #5 (**permalink**) 01-18-06, 15:26

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

sathyaram_s · #6 (**permalink**) 01-18-06, 15:33

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

JDionne · #7 (**permalink**) 01-18-06, 16:44

Ya im sure its the key to the table

JDionne · #8 (**permalink**) 01-19-06, 14:49

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

sathyaram_s · #9 (**permalink**) 01-19-06, 14:53

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

JDionne · #10 (**permalink**) 01-19-06, 14:58

test test test

JDionne · #11 (**permalink**) 01-19-06, 15:13

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) ;

JDionne · #12 (**permalink**) 01-25-06, 13:59

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.

Wim · #13 (**permalink**) 01-26-06, 07:11

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) ;

Internet Services

Web Development

Digital Marketing

Consumer Tech