asked on

SQL-Statement. Convert 'NULL' to 0

i have the following update:

UPDATE test2
SET test2.id= (select max(test1.id) from test1 where test1.bez = test2.bez);

now the select '(select max(test1.id) from test1 where test1.bez = test2.bez);' can return 'NULL'. But in table test2.id NULL is not allowed. So i must convert 'NULL' to 0.

like

UPDATE test2
SET test2.id= (select ConvertNULL(max(test1.id)) from test1 where test1.bez = test2.bez);

or the select should not update 'NULL' values but i dont want a select like because i think its to slow:

UPDATE test2
SET test2.id= (select max(test1.id) from test1 where test1.bez = test2.bez)
where (select max(test1.id) from test1 where test1.bez = test2.bez) is not NULL;

rramineni

How about using nvl function instead of complicating the statements.

UPDATE test2
SET test2.id= (select nvl(max(test1.id),0) from test1 where test1.bez = test2.bez);

or

UPDATE test2
SET test2.id= (select max(nvl(test1.id,0)) from test1 where test1.bez = test2.bez);

ASKER CERTIFIED SOLUTION

andrewst

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

timemasterx

ASKER

thanks, all solutions worked but i use this one

UPDATE test2
SET test2.id= (select max(test1.id) from test1 where test1.bez = test2.bez)
where exists (select 1 from test1 where test1.bez = test2.bez and test1.id is not NULL);

becase it works under MS SQL-Server too.