CODEWITHSUNDEEP
sql-serversql-server-2008null
mikebmassey
mikebmassey

Reputation: 8604

Getting NULLs out of an entire table

I have a SQL Server table that I am building with over 100 columns. I am running UPDATE TABLE_NAME queries to populate that table. However, if there are no records available, a NULL is inserted into the record. This makes the calculations that I must perform difficult .

I tried creating the table with NOT NULL and DEFAULT (0) in the CREATE TABLE statement, but when I run an update, it tries to insert a NULL value and I get this error:

Update Statement:

UPDATE TABLE_NAME
SET NumOrders = (SELECT count(OrderID) FROM ORDERS WHERE TABLE_NAME.CUSTOMER_ID = ORDERS.CUSTOMER_ID);

Cannot insert the value NULL into column 'NumOrders', table 'TABLE_NAME'; column does not allow nulls. UPDATE fails.

I could run a 2nd update query to remove the NULLS, such as:

UPDATE TABLE_NAME
SET NumOrders = 0
WHERE NumOrders IS NULL;

This seems like I am doubling up on the update queries I have to write.

There has to be a better way to do this, right....?

Thanks

Upvotes: 0

Views: 128

Answers (3)

Alok
Alok

Reputation: 274

UPDATE TABLE_NAME SET NumOrders = COALESCE((SELECT count(OrderID) FROM ORDERS WHERE TABLE_NAME.CUSTOMER_ID = ORDERS.CUSTOMER_ID),0);

Upvotes: 0

AndrewBay
AndrewBay

Reputation: 938

UPDATE TABLE_NAME
SET NumOrders = isnull((SELECT count(OrderID) FROM ORDERS WHERE TABLE_NAME.CUSTOMER_ID = ORDERS.CUSTOMER_ID),0);

does this work for you?

Upvotes: 3

SQLMenace
SQLMenace

Reputation: 135171

default constraints only fire on inserts not updates, another option for you is to have an update trigger that will change the NULLS to something that is not null

or specify the values in your update statement and make sure it is not null but an actual value

Upvotes: 1

Related Questions