Geek asks and answers

Mysql - delete rows if null exists more than a certain number

I have a table like:

id | name1 | name2 | name3
1 | asa | NULL | das
2 | NULL | NULL | asas

I want to delete each row with two or more times NULL (here, with id = 2)
I already did this with a little PHP script, but I am wondering if this can be done using mysql query

I am new to mysql, so I have not tried anything yet!

+5
source share
4 answers

You need to use a sentence WHEREwith several filters, each of which checks if the column is null:

delete 
from yourtable
where 
  (name1 is null and name2 is null) or
  (name1 is null and name3 is null) or
  (name2 is null and name3 is null) or
  (name1 is null and name2 is null and name3 is null)

See SQL Fiddle with Demo

+4
source 
delete from table where 
     (name1 is null AND name2 is null) OR
     (name2 is null AND name3 is null) OR
     (name1 is null AND name3 is null) OR
     (name1 is null AND name2 is null AND name3 is null)
+1
source

IS NULL (0 1) , , 2.

:

delete from your_table
where ((name1 is null) + (name2 is null) + (name3 is null)) >= 2
+1

Related, if you need to delete rows where ALL columns are zero, you can do this.

The procedure will delete any row for all columns that are empty, ignoring the main column, which can be set as an identifier.

DELIMITER //
CREATE PROCEDURE DeleteRowsAllColNull(IN tbl VARCHAR(64))
BEGIN
SET @tbl = tbl;
SET SESSION group_concat_max_len = 1000000;
SELECT CONCAT('DELETE FROM `',@tbl,'` WHERE ',(REPLACE(group_concat(concat('`',COLUMN_NAME, '` is NULL')),',',' AND ')),';') FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = @tbl AND COLUMN_KEY NOT LIKE 'PRI' into @delete_all;
PREPARE delete_all FROM @delete_all;
EXECUTE delete_all;
DEALLOCATE PREPARE delete_all;
END //
DELIMITER ;

Follow the procedure below.

CALL DeleteRowsAllColNull('your table');
0
source

More articles:


All Articles