CODEWITHSUNDEEP
mysql
Achilles
Achilles

Reputation: 504

MySQL - Removing null value rows from table

I have a table "user" with over 60 columns. One of the column's name is "username"

I want to remove the rows where the username field is empty or NULL

How can I do this?

Thank you!

Upvotes: 12

Views: 44510

Answers (3)

Akila Viduranga Liyanaarachchi
Akila Viduranga Liyanaarachchi

Reputation: 23

Here I have created a script for any kind of SQL table. please copy this stored procedure and create this on your Environment and run this stored procedure with your Table.

exec [dbo].[SP_RemoveNullValues] 'Your_Table_Name'

stored procedure

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--akila liyanaarachchi
Create procedure [dbo].[SP_RemoveNullValues](@PTableName Varchar(50) ) as 
begin


DECLARE Cussor CURSOR FOR 
SELECT COLUMN_NAME,TABLE_NAME,DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @PTableName  

OPEN Cussor;

Declare @ColumnName Varchar(50)
Declare @TableName  Varchar(50)
Declare @DataType Varchar(50)
Declare @Flage  int 

FETCH NEXT FROM Cussor INTO @ColumnName,@TableName,@DataType
WHILE @@FETCH_STATUS = 0
BEGIN

set @Flage=0


If(@DataType in('bigint','numeric','bit','smallint','decimal','smallmoney','int','tinyint','money','float','real'))
begin
set @Flage=1
end 
If(@DataType in('date','atetimeoffset','datetime2','smalldatetime','datetime','time'))
begin
set @Flage=2
end 
If(@DataType in('char','varchar','text','nchar','nvarchar','ntext'))
begin
set @Flage=3
end 

If(@DataType in('binary','varbinary'))
begin
set @Flage=4
end 



DECLARE @SQL VARCHAR(MAX) 

if  (@Flage in(1,4))
begin 

SET @SQL ='  update ['+@TableName+'] set ['+@ColumnName+']=0 where ['+@ColumnName+'] is null'
end 

if  (@Flage =3)
begin 

SET @SQL ='  update ['+@TableName+'] set ['+@ColumnName+'] = '''' where ['+@ColumnName+'] is null '
end 

if  (@Flage =2)
begin 

SET @SQL ='  update ['+@TableName+'] set ['+@ColumnName+'] ='+'''1901-01-01 00:00:00.000'''+' where ['+@ColumnName+'] is null '
end 


EXEC(@SQL)



FETCH NEXT FROM Cussor INTO @ColumnName,@TableName,@DataType
END

CLOSE Cussor
DEALLOCATE Cussor

END

Upvotes: 0

Deepak Rai
Deepak Rai

Reputation: 2203

If you want to delete all those rows containing username = NULL AND where username is empty string ("") as well

then 

DELETE FROM table_name WHERE username IS NULL OR username = '';

It is advised to first do a SELECT query with same WHERE condition as that you are going to use in DELETE query to see which rows will be deleted:

SELECT * FROM table_name WHERE username IS  NULL OR username = "";

Upvotes: 7

Nagaraj S
Nagaraj S

Reputation: 13474

Try this

DELETE FROM user WHERE username IS NULL;

or

DELETE FROM user WHERE username = '';

Problems with NULL Values

Upvotes: 22

Related Questions