Sql-server – Find a row with value zero in any column

sql serversql-server-2016

I’ve 10,000 rows with 64 columns.

If any column in any row has the value “0”, that row needs to be selected. Does anyone have a ready-made query? It would be a benefit to my project.

The values can be null. The value cannot be negative. It is an int value. The version is SQL Server 2016.

Best Answer

Too many variants:

WHERE column1=0 OR column2=0 OR ... OR column64=0
WHERE column1*column2* ... *column64=0
WHERE 0 IN (column1, column2, ... column64)

and so on...

Related Solutions

SQL Server Update Column with Datetime – How to Use Row Values

It looks like you have at least two different regional date formats there, possibly USA and Europe? You could just split the updates by languages...

something like this, which uses the language setting to control isdate and convert.

set language english;
update test_dates 
set ToDateTime = convert(datetime,ContainedData)
where AID='123' and PID='2' and isdate(ContainedData)=1;

set language british;
update test_dates 
set ToDateTime = convert(datetime,ContainedData)
where AID='123' and PID='2' and isdate(ContainedData)=1;

If the table is too large to update in big chunks, then you should look at wrapping that in a loop with a row limit and a transaction around each update and repeat until zero rows have been updated.

Sql-server – Cannot insert the value NULL into column

I didn't take a really hard look at this but you're defining the DBName, UserName, and LoginType columns in @DB_USers as sysname. The type sysname is defined as NOT NULL.

What is SYSNAME data type in SQL Server?

As to why you're getting a NULL value you're going to have to check the dataset you're running against.

Best Answer

Related Solutions

SQL Server Update Column with Datetime – How to Use Row Values

Sql-server – Cannot insert the value NULL into column

Related Question