Null Value for Int in Update Statement

NULL value for int in Update statement

Assuming the column is set to support NULL as a value:

UPDATE YOUR_TABLE
   SET column = NULL

Be aware of the database NULL handling - by default in SQL Server, NULL is an INT. So if the column is a different data type you need to CAST/CONVERT NULL to the proper data type:

UPDATE YOUR_TABLE
   SET column = CAST(NULL AS DATETIME)

...assuming column is a DATETIME data type in the example above.

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)

MS SQL update query on int column with an empty value

You are passing a string instead of NULL to the server. Get rid of the apostrophes (') for col1 and col2.

Update INT/DECIMAL field with a null/no value

Set $variable1 to "NULL" (the string). This way it will end up in the query as NULL, which is what represents NULL in SQL.

Update with NULL for a Numeric field with a textfield in a form

Sorry I got started on this, I thought it was VB.NET and not Access. Let me know if this works:

Private Sub txtPortNumber_AfterUpdate()
    Dim portSQL As String
    Dim portNumber as Integer
    Dim myID as Integer 

    If IsNumeric(Me.txtID3) Then 
        myID = CInt(Me.txtID3) 

        If IsNumeric(Me.txtPortNumber) Then
            portNumber = CInt(Me.txtPortNumber) 
            portSQL = "UPDATE Switches SET [Port Number] = " & portNumber & " WHERE ID = " & myID  & ""
        Else
            portSQL = "UPDATE Switches SET [Port Number] = NULL WHERE ID = " & myID  & ""
        End If 
        DoCmd.RunSQL (portSQL)

    End If
    Me.Refresh
End Sub

How can I not send Null to database while using Update

You almost have it and as Alex K pointed out in the comment above, the most elegant way to handle optional updates is to allow null parameters and update what is sent over.

ALTER PROCEDURE [dbo].[Customer_update] 
    (@Id INT,
     @Firstname NVARCHAR(40) = NULL,
     @Lastname NVARCHAR(40) = NULL,
     @City NVARCHAR(40) = NULL,
     @Country NVARCHAR(40) = NULL,
     @Phone NVARCHAR(20) = NULL)
AS
BEGIN
    UPDATE Customer 
    SET FirstName = ISNULL(@Firstname, FirstName)... 
    WHERE Id = @Id 
END

In order to use this in client code with nullable fields, simply omit the parameter altogether or let nulls pass through (one caveat to this is when you really need to set the field to null in which case the field should probably not be nullable. Then you can implicitly send a null over and the field will be set to the proper value)

Nullable fields allow you to omit parameters from a stored procedure call and still perform the operation. This is useful when you need make changes and do not want to impact existing applications, as long as the nullable fields are not required.

Are you asking about Insert/Update in one operation?

Some people do "upserts". I personally do not like having one operation for insert/updates. I feel the client should already know what operation should be performed, also, having one update and one insert procedure makes the procedures more transparent and easier to auto generate.

However, if that is what you were asking then you would create a procedure similar to the one below:

ALTER PROCEDURE [dbo].[Customer_update] 
    (@Id INT = NULL,
     @Firstname NVARCHAR(40) = NULL,
     @Lastname NVARCHAR(40) = NULL
     @City NVARCHAR(40) = NULL
     @Country NVARCHAR(40) = NULL
     @Phone NVARCHAR(20) = NULL)
AS
BEGIN
    IF (@Id IS NULL) 
    BEGIN
        INSERT INTO Customer...
        SET @ID = @@SCOPE_IDENTITY
    END 
    ELSE BEGIN
        UPDATE Customer 
        SET FirtName = ISNULL(@FirstName, FirstName)
        WHERE Id = @Id
    END

    SELECT * 
    FROM Customer 
    WHERE Id = @Id
END

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

Updating integer column with null values in postgres

This should be,

UPDATE table1 
SET column_a = NULL
WHERE column_b = 'XXX';

---

Related Topics