Update Columns with Null Values

How to update column with null value

No special syntax:

CREATE TABLE your_table (some_id int, your_column varchar(100));

INSERT INTO your_table VALUES (1, 'Hello');

UPDATE your_table
SET    your_column = NULL
WHERE  some_id = 1;

SELECT * FROM your_table WHERE your_column IS NULL;
+---------+-------------+
| some_id | your_column |
+---------+-------------+
|       1 | NULL        |
+---------+-------------+
1 row in set (0.00 sec)

How do I set a column value to NULL in SQL Server Management Studio?

If you've opened a table and you want to clear an existing value to NULL, click on the value, and press Ctrl+0.

Altering a column to be nullable

Assuming SQL Server (based on your previous questions):

ALTER TABLE Merchant_Pending_Functions ALTER COLUMN NumberOfLocations INT NULL

Replace INT with your actual datatype.

Update null values by value in same column

You can use windowed version of SUM function in order to determine islands of NULL valued records along with the record having the higher ID in the same group:

SELECT [Group], ID, Value, 
       SUM(CASE WHEN Value IS NULL THEN 0 ELSE 1 END) OVER 
       (PARTITION BY [Group] ORDER BY ID DESC) AS grp
FROM mytable

Output:

Group   ID  Value   grp
-----------------------
A       4   40      1
A       3   30      2
A       2   NULL    2
A       1   NULL    2
B       4   40      1
B       3   NULL    1
B       2   20      2
B       1   10      3

You can now wrap the above query in a CTE and use another CTE to do the update:

;WITH CTE AS (
SELECT [Group], ID, Value, 
       SUM(CASE WHEN Value IS NULL THEN 0 ELSE 1 END) OVER 
       (PARTITION BY [Group] ORDER BY ID DESC) AS grp
FROM mytable
), ToUpdate AS (
   SELECT [Group], ID, Value,  
          MAX(Value) OVER (PARTITION BY [Group], grp) AS group_value  
   FROM CTE
)
UPDATE ToUpdate
SET Value = group_value
WHERE Value IS NULL

Demo here

Edit:

The above query doesn't handle the edge case where the very last record within a Group slice is NULL. To handle this case as well you can use the following query:

;WITH CTE AS (
SELECT [Group], ID, Value, 
       SUM(CASE WHEN Value IS NULL THEN 0 ELSE 1 END) OVER 
       (PARTITION BY [Group] ORDER BY ID DESC) AS grp,
       SUM(CASE WHEN Value IS NULL THEN 0 ELSE 1 END) OVER 
       (PARTITION BY [Group] ORDER BY ID) AS grp2
FROM mytable
), ToUpdate AS (
   SELECT [Group], ID, Value,            
          MAX(Value) OVER (PARTITION BY [Group], grp) AS group_value,  
          MAX(Value) OVER (PARTITION BY [Group], grp2) AS group_value2  
   FROM CTE
)
UPDATE ToUpdate
SET Value = COALESCE(group_value, group_value2)
WHERE Value IS NULL

Demo here

Update columns with Null values

Change it to

...where logouttime is null;
                    ^^^^^^^

NULL is a special value and we cannot use the usual = operator with it.

From the Oracle documentation for NULL:

To test for nulls, use only the comparison conditions IS NULL and IS NOT NULL. If you use any other condition with nulls and the result depends on the value of the null, then the result is UNKNOWN because null represents a lack of data, a null cannot be equal or unequal to any value or to another null

---

Related Topics