CODEWITHSUNDEEP
sql
semper fi
semper fi

Reputation: 737

insert data where all rows equals to null

Is there a way to insert data where all rows are equals to null? I know it looks like 

select login from Users
where login is null

When it return me 

1. null
2. null
3. null

and so on...

How can I fill data to all this null rows?

Upvotes: 0

Views: 87

Answers (3)

Rich Benner
Rich Benner

Reputation: 8113

To just update the null values in one column then do this;

UPDATE TableName
SET FieldName = ISNULL(FieldName,'NewValue')

If you want to update all columns when they ALL are NULL then you can do something like this

UPDATE TableName
SET 
     Field1 = 'Value1'
    ,Field2 = 'Value2'
    ,Field3 = 'Value3'
WHERE
    Field1 IS NULL 
    AND Field2 IS NULL
    AND Field3 IS NULL

If you want to replace all NULL values in the table regardless of whether the whole row is NULL then you can do this;

UPDATE TableName
SET
     Field1 = ISNULL(Field1,'Value1')
    ,Field2 = ISNULL(Field2,'Value2')
    ,Field3 = ISNULL(Field3,'Value3')

If you want to update the values to different values for each row you will need a way of linking a user to their login. A basic version would be something like this (I'm referring to this as LookupTable)

ID  Login
1   User1
2   User2
3   User3

Your query will be something like this;

UPDATE a
SET a.login = ISNULL(a.login,b.login)
FROM TableName a
JOIN LookupTable b
    ON a.id = b.id

This will only update values with a NULL but you'd probably want to just set a.login = b.login to ensure that all of your data is correct.

Upvotes: 3

Maria Ivanova
Maria Ivanova

Reputation: 1146

UPDATE Users 
SET login = 'value'
WHERE login is NULL

This would update the login column with the value 'value', if the current value is NULL.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269593

Assuming that all columns in a table can take NULL values and do not have defaults, you can insert a row into a table with all NULL values by doing:

insert into t(col)
    select NULL;

insert should have at least a single column; any column will do.

Upvotes: 0

Related Questions