Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 4746
  • Last Modified:

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;

0
timemasterx
Asked:
timemasterx
1 Solution
 
rramineniCommented:
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);
0
 
andrewstCommented:
To avoid updating the rows where the MAX is NULL:

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

Or to set the ID to 0:

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



0
 
timemasterxAuthor Commented:
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.

0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now