CODEWITHSUNDEEP
sqlsql-server
Jean
Jean

Reputation: 1490

select multiple columns which are null for a row

I have 1 id column and 3 int columns. Data is inserted in this way

insert into table (col1, col2) values (1,1) 

insert into table (col2, col3) values (1,1)

insert into table (col1, col3) values (1,1)

insert into table (col1) values (1)

then, row 1, col 3 is null row 2, col 1 is null, row 3, col 2 is null. row 4, col 2 and col 3 are null.

I want to replace the null values with 0. How can I do that?

Note: I won't know which columns are null, so i can't use ISNULL(colname, 0)

Upvotes: 0

Views: 1414

Answers (5)

Jesuraja
Jesuraja

Reputation: 3844

If you want to insert 0 to columns by default, you can alter the table and set DEFAULT CONSTRAINT to needed column

ALTER TABLE myTable ADD DEFAULT(0) FOR Col1

No UPDATE statement required

Anyhow you need to update existing records using UPDATE statement.

Upvotes: 2

Jean
Jean

Reputation: 1490

I'll accept Gordon's, but in the end I did this, because my number of columns can change and I wanted it to be specific to certain rows. 

CREATE PROCEDURE [setNullToZero]
    @rowSID VARCHAR(60) = 'A'
AS
BEGIN
declare @column_name as varchar(100)
declare col cursor for
SELECT column_name
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_name = 'dlgroups' 
ORDER BY ordinal_position;


open col;
fetch next from col into @column_name;
while @@FETCH_STATUS =0
begin
if (@column_name != 'id')
    exec('Update dlgroups set '+ @column_name +'=ISNULL('+@column_name+',0) WHERE sid='''+@rowSID+'''')

fetch next from col into @column_name
end
close col
deallocate col
END
GO

Upvotes: 0

Splunk
Splunk

Reputation: 311

When you want to replace a possibly null column with something else, use IsNull.

 SELECT ISNULL(myColumn, 0 ) FROM myTable

Something like this:

SELECT ISNULL(COALESCE(Col1, Col2, Col3),0) FROM Table

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271111

If you want to update the table so the NULLs become 0, use coalesce():

update table t
    set col1 = coalesce(col1, 0),
        col2 = coalesce(col2, 0),
        col3 = coalesce(col3, 0)
    where col1 is null or col2 is null or col3 is null;

coalesce() is the ANSI-standard function for replacing a NULL value with something else.

Upvotes: 3

DeanOC
DeanOC

Reputation: 7282

Update table set co1 = ISNULL(col1,0), col2 = ISNULL(col2, 0), col3 = ISNULL(col3, 0)

if the value in col1, 2 or 3 is not null then it will be replaced with the same value, otherwise if null then it will be replaced with 0.

Upvotes: 1

Related Questions