Can't update from NULL to 0

Question

Hi i'm using this command to left join a table with another table that has a little more records but it keep printing NULL. I want that NULL to become 0.

SELECT * FROM TABLE1

Left JOIN Table2 ON TABLE1.ID=Table2.IDRel

UPDATE Table2 SET IDRel = 0 where IDRel = NULL

as @xQbert answered, the problem is likely to be that you have rows in Table1 with no matching rows in Table2, so the update won't make sense - you probably just want to use his solution of coalescing the null values in the LEFT JOIN output to 0. — mc110, Jun 26 '14 at 13:11

DrCopyPaste · Accepted Answer · 2014-06-26T13:29:24.733

4

The update should not be needed. As you said Table2 has a little more records that's the key thing here, that means that for any row from Table1 for which no matching IDRel value could be found that column (IDRel) will stay NULL.

Now you could for example use ISNULL(IDRel, 0) to replace null value with 0, but maybe an INNER JOIN instead of the LEFT JOIN could get you the right result throwing out all rows that could not be matched...

However... If you really were to update that column it would only work if you did the correct comparison against NULL (that is IS not =) that would mean changing your update query into:

UPDATE Table2 SET IDRel = 0 where IDRel IS NULL

edited Jun 26 '14 at 13:29

answered Jun 26 '14 at 13:00

DrCopyPaste

4,023
1
22
57

2

I think the problem is that the LEFT JOIN creates NULLs, not that table2 contain NULLs – adrianm Jun 26 '14 at 13:02
@adrianm you are right,left join creates the null and i want them to be 0 or anything else but not NULL – Didy Jun 26 '14 at 13:07
Right, sorry, skimmed a little too fast through your question there, I updated my answer to account for both possibilities. – DrCopyPaste Jun 26 '14 at 13:14

score 2 · Answer 2 · answered Jun 26 '14 at 13:05

2

IS the update even needed?

SELECT T1.*, coalesce(t2.IDREL,0) as IDREL 
FROM TABLE1 T1
Left JOIN Table2 ON TABLE1.ID=Table2.IDRel

answered Jun 26 '14 at 13:05

xQbert

34,733
2
41
62

score 0 · Answer 3 · answered Jun 26 '14 at 13:01

0

Use this query

 UPDATE Table2 SET IDRel = 0 where IDRel is NULL

answered Jun 26 '14 at 13:01

christof13

329
1
10

score 0 · Answer 4 · answered Jun 26 '14 at 13:01

0

use IS NUll

SELECT * FROM TABLE1

Left JOIN Table2 ON TABLE1.ID=Table2.IDRel

UPDATE Table2 SET IDRel = 0 where IDRel IS  NULL

answered Jun 26 '14 at 13:01

Nagaraj S

13,316
6
32
53

Can't update from NULL to 0

4 Answers4