Updating a large table

[Hevan]

Hi,

I am using this sql for updating a large table.

This sql should update a record like 'abc123'. The first select will return 'abc' and the second select will return '123'.
This works fine if the second select is not null.
If the second select returns null then the updated record should be a.p_cd = 'abc'. But it shows null.

If i use two seperate updates It works fine. But i want to use one update
query:

update tab1 a set a.p_cd = (select b.p_cd from tab2 b where b.type = a.type fetch first row only)
|| (select c.p_type from tab3 c where a.no = c.no fetch first row only)
where exists (select 1 from tab3 c where a.no = c.no fetch first row only)

Please let me know if you have any ideas.

Thanks,
Hevan

[frozenmist]

Hi Hevan,
This is because when a string is concatenated with a null value in DB2, the result will be NULL.
you can use value to solve it..

 
 
update tab1 a set  a.p_cd = (select value(b.p_cd,'' ) from tab2 b where b.type = a.type fetch first row only)
 ||   (select value(c.p_type,'' ) from tab3 c where a.no = c.no fetch first row only) 
where   exists (select 1 from tab3 c where  a.no = c.no fetch first row only) 
 
 

Try out this small change...

Hope it helps
Cheers

[Hevan]

Hi Frozenmist,

Thankyou for your help. Still, It updates spaces if the second select is null.

Instead of abc(with spaces)

Thanks,
Hevan

Hi Hevan,
This is because when a string is concatenated with a null value in DB2, the result will be NULL.
you can use value to solve it..

 
 
update tab1 a set  a.p_cd = (select value(b.p_cd,'' ) from tab2 b where b.type = a.type fetch first row only)
 ||   (select value(c.p_type,'' ) from tab3 c where a.no = c.no fetch first row only) 
where   exists (select 1 from tab3 c where  a.no = c.no fetch first row only) 
 
 

Try out this small change...

Hope it helps
Cheers

[frozenmist]

Hi Hevan,
I didnt understand the problem. Can you be more clear...

Cheers

[Hevan]

Hi,

I have 3 tables which i have to update tab1 using this update.

tab1 (no integer, type varchar(5), p_cd varchar(10))

values
10, abc1
10, abc1
20, aaa1
20, aaa1
30, bbb1

tab2(p_cd(5), type(5)
values
abc, abc1
aaa, aaa1
bbb, bbb1
cc, ccc2

tab3(no integer, p_type varchar(5)
values
10, 123
10, 123
20, 222
20, 222

UPDATE tab1 a SET a.p_cd = (SELECT value(b.p_cd,'' ) FROM tab2 b WHERE b.type = a.type fetch first row only)
|| (SELECT value(c.p_type,'' ) FROM tab3 c WHERE a.no = c.no fetch first row only)
WHERE EXISTS (SELECT 1 FROM tab3 c WHERE a.no = c.no fetch first row only)

if i run the update sql i should get 10, abc1, abc123
10, abc1, abc123
20, aaa1, aaa222
20, aaa1, aaa222
30, bbb1, bbb


instead of this i get 10, abc1, abc123
10, abc1, abc123
20, aaa1, aaa222
20, aaa1, aaa222
30, bbb1,

[frozenmist]

Hi,
Ok. I get it.
Try this

 
UPDATE tab1 a SET  a.p_cd = value((SELECT b.p_cd FROM tab2 b WHERE b.type = a.type fetch first row only),'')
||   value((SELECT c.p_type FROM tab3 c WHERE a.no = c.no fetch first row only),'')
    WHERE   EXISTS (SELECT 1 FROM tab3 c WHERE  a.no = c.no fetch first row only)
 
 
 

Hope it works ...
Cheers!!!

[Hevan]

Hi,

UPDATE tab1 a SET a.p_cd = value((SELECT b.p_cd FROM tab2 b WHERE b.type = a.type fetch first row only),'')
|| value((SELECT c.p_type FROM tab3 c WHERE a.no = c.no fetch first row only),'')


It worked . Great!

Thankyou,
Hevan