mysql - delete rows if null exist more than a specific num

Question

I have a table like :

id | name1 | name2 | name3

 1 |  asa  |  NULL |  das

 2 |  NULL |  NULL |  asas

I want to delete every row that has two or more time the NULL value (here, the one with id = 2)
I already did that with a small PHP script but i wonder if that can be done with a mysql query

I am new to mysql so i didn't try anything yet!

Ironically, this question is a duplicate :P http://stackoverflow.com/questions/2630440/how-to-delete-duplicates-on-mysql-table — Benjamin Gruenbaum, Mar 28 '13 at 19:53
@BenjaminGruenbaum I disagree - the linked question asks how to delete duplicates, whereas this question is about deleting non-duplicate rows that match certain criteria (here: at least two out of three columns are NULL) — Frank Schmitt, Mar 28 '13 at 20:04
@FrankSchmitt I did not say it is an exact duplicate, it's very closely related though imo. Valid point. — Benjamin Gruenbaum, Mar 28 '13 at 20:05

score 5 · Accepted Answer · answered Mar 28 '13 at 19:57

You will want to use a WHERE clause with multiple filters, each one checking is 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

score 1 · Answer 2 · answered Mar 28 '13 at 19:54

1

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)

answered Mar 28 '13 at 19:54

ajspacemanspiff

478
1
10
21

This is the exact same answer as given by @bluefeet♦ – Monica Heddneck Mar 06 '17 at 20:52
And you'll note that I posted it 3 minutes earlier. – ajspacemanspiff Mar 07 '17 at 21:30

score 1 · Answer 3 · answered Mar 28 '13 at 20:10

1

You can use IS NULL to get a boolean (0 or 1) for each column, sum those results and delete the rows where the sum is greater than or equal to 2.

Like this:

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

answered Mar 28 '13 at 20:10

Ike Walker

64,401
14
110
109

score 1 · Answer 4 · answered Feb 14 '16 at 12:52

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

The procedure will delete any row for all columns that are null ignoring the primary column that may be set as an ID.

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 ;

Execute the procedure like this.

CALL DeleteRowsAllColNull('your table');

mysql - delete rows if null exist more than a specific num

4 Answers4