CODEWITHSUNDEEP
mysql
Manos Serifios
Manos Serifios

Reputation: 577

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

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!

Upvotes: 6

Views: 13248

Answers (4)

STWilson
STWilson

Reputation: 1708

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');

Upvotes: 1

Ike Walker
Ike Walker

Reputation: 65547

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

Upvotes: 1

Taryn
Taryn

Reputation: 247720

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

Upvotes: 5

ajspacemanspiff
ajspacemanspiff

Reputation: 478

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)

Upvotes: 1

Related Questions