Troubleshooting Snowflake SQL Error : NULL result in a non-nullable column
When working with Snowflake, you might encounter the error message:
SQL Error [100072] [22000]: NULL result in a non-nullable column
This error can be misleading at first glance. Let's walk through a common scenario that triggers this error and how to resolve it.
Scenario
Consider the following SQL script:
-- Create the test table
CREATE TABLE set_test (
id INT NOT NULL,
is_deleted CHAR(1) NOT NULL,
foo FLOAT NULL
);
-- Insert a few records
INSERT INTO set_test (id, is_deleted, foo) VALUES
(1, 'F', 10.5),
(2, 'F', 20.0),
(3, 'F', 30.75);
-- Update one record to set is_deleted to 'T' and foo to NULL
UPDATE set_test
SET is_deleted = 'T' and foo = NULL
WHERE id = 2;
When you run the UPDATE
statement, Snowflake returns the error message:
SQL Error [100072] [22000]: NULL result in a non-nullable column
Understanding the Error
The error message indicates that there is an attempt to insert a NULL
value into a column that does not allow NULL
values. However, in this case, the error is not due to the NULL
value itself but rather a syntax issue in the UPDATE
statement.
Resolving the Error
The issue lies in the UPDATE
statement:
UPDATE set_test
SET is_deleted = 'T' and foo = NULL
WHERE id = 2;
In SQL, the SET
clause should use commas to separate multiple column assignments, not the AND
keyword. The correct UPDATE
statement should be:
UPDATE set_test
SET is_deleted = 'T', foo = NULL
WHERE id = 2;
By replacing and
with a comma, the UPDATE
statement will execute without errors:
UPDATE set_test
SET is_deleted = 'T', foo = NULL
WHERE id = 2;
Conclusion
When you encounter the "NULL result in a non-nullable column" error in Snowflake, double-check the syntax of your UPDATE
statement. Ensure that you are using commas to separate column assignments in the SET
clause. This simple fix can save you time and prevent confusion when troubleshooting SQL errors.
If this does not help, see also Snowflake KB Inserting or loading data in table fails with error "NULL result in a non-nullable column".