CODEWITHSUNDEEP
mysqlsql
EmbraceNext
EmbraceNext

Reputation: 75

Delete row in MySQL if any column has NULL value?

Is there a way to delete a row if any of the columns have a NULL value in them? I know I could do it one by one and check the columns but I would like to do this programmatically in MySQL where it would scale if I had 4 columns or 4000 columns. I believe I could do this with PHP, but I much rather do this in straight MySQL.

Thank you

Upvotes: 0

Views: 2973

Answers (3)

Jaugar Chang
Jaugar Chang

Reputation: 3196

You could delete those records with without so much ORs:

DELETE FROM myTable
WHERE CONCAT(column1,column2,column3) is null

It may not make sense to delete what can be done, but can use this trick to get what should be done.

INSERT INTO NEW_TABLE
SELECT column1,column2,column3
FROM myTable
WHERE not CONCAT(column1,column2,column3) is null

Upvotes: 2

Markus
Markus

Reputation: 693

I am not quite sure if this works in mysql because i can't test it (i edited the sql server code to may work with mysql)

CREATE PROCEDURE myProc()
    BEGIN

     DECLARE COL varchar(4000);
     SELECT GROUP_CONCAT(C.COLUMN_NAME, ' IS NULL ' SEPARATOR ' OR ') FROM INFORMATION_SCHEMA.COLUMNS C WHERE C.TABLE_NAME = 'tbl_a' INTO COL;
     SET @s = CONCAT('SELECT * FROM test WHERE ', COL);
     PREPARE stmt FROM @s; 
     EXECUTE stmt;
     DEALLOCATE PREPARE stmt;

END//

i am using the select statement to check you just need to change that to delete

Upvotes: 0

Hanky Panky
Hanky Panky

Reputation: 46900

Ok, since you just mentioned you are new to MySQL, your database design is new too and most probably does not have a lot of data as of now.

Why not kill the roots of the problem instead of letting those grow into a big tree and then looking for tools to cut all the branches first?

You should go ahead and use MySQL NOT NULL option and disallow null values for your column since you are deleting them. So if you don't need to keep any null values then you can simply disallow them and they will not be saved in the first place.

Queries come long after a proper database design, if your design does not match what your system requires then you can only optimize the queries to an extent. Base structure is the first thing you should learn and improve. Google and SO both are filled with thousands of articles on Efficient database design and some basic concepts to get started.

Upvotes: 2

Related Questions