SQL Server - ISNULL not working on Update Query

Question

Rather than have a NULL in a column, I want a 0 to be present.

Given the following two tables:

TABLE1
ClientID        OrderCount
1               NULL
2               NULL
3               NULL
4               NULL


Table2
ClientID        OrderCount
1               2
3               4
4               6

NOTE: The OrderCount column in both tables is INT datatype.

UPDATE TABLE1
SET OrderCount = ISNULL(TABLE2.OrderCount,0)
FROM TABLE1
INNER JOIN TABLE2 ON TABLE2.ClientID = TABLE1.CLIENTID

When I look at table1, I see this:

ClientID        OrderCount
1               2
2               NULL
3               4
4               6

So, I thought to myself - "Obviously, I should be using NULLIF and not ISNULL", so I reversed them. Same result.

What am I doing wrong here? How do I get a 0 rather than a NULL in the column?

mellamokb · Accepted Answer · 2012-03-27 21:38:05Z

6

You need a LEFT JOIN rather than an INNER JOIN. The records that don't have a matching ClientID are not even being touched by your query.

answered Mar 27, 2012 at 21:38

mellamokb

56.9k12 gold badges111 silver badges138 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Diego · Accepted Answer · 2012-03-27 21:39:32Z

2

you are using INNER JOIN but you don't have client ID 2 on table2 so your result set wont include a line with 2. Replace it with LEFT JOIN

answered Mar 27, 2012 at 21:39

Diego

36.5k21 gold badges94 silver badges138 bronze badges

Comments

usr · Accepted Answer · 2012-03-27 21:39:19Z

0

Your join is probably filtering out rows.

answered Mar 27, 2012 at 21:39

usr

172k38 gold badges251 silver badges380 bronze badges

Collectives™ on Stack Overflow

SQL Server - ISNULL not working on Update Query

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related