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)
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.
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.
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.
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
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
ISNULLon each column. What's the problem?ISNULLorCOALESCEfor each column to be updated.