11

Is there a way to delete a row from a table where any of the column field is null without specifying explicitly which column is null?

I am using postgreSQL.

Here's my relation schema:

  Column    |  Type   |                              Modifiers                               
  --------------+---------+----------------------------------------------------------------------
  id           | integer | not null default  nextval('aurostat.visitor_center_id_seq'::regclass)
  date         | date    | 
  persons      | integer | 
  two_wheelers | integer | 
  cars         | integer | 
  vans         | integer | 
  buses        | integer | 
  autos        | integer | 

Thanks

18

I see two ways of doing that:

With plain standard SQL, simply list all columns and combine that with an OR:

delete from the_table
where date is null
   or persons is null
   or two_wheelers is null
   or cars is null
   or vans is null
   or buses is null
   or autos is null;

Another (Postgres specific) solution is the comparison of the whole row with NOT NULL

select *
from the_table
where the_table is not null;

will return only rows where all columns are not null. You want the opposite, so you need to negate that where not (the_table is not null) The condition where the_table is null is something different - that only matches rows where all columns are null.

delete from the_table
where not (the_table is not null);
|improve this answer|||||
  • Thank you! I think the second solution is the solution that I was looking for. – dhaliman Jul 15 '16 at 6:56
  • 3
    that's ingenious – Jack says try topanswers.xyz Jul 15 '16 at 8:20
  • I really like the clear and concise where not (the_table is not null); approach. Best what I can think of in general SQL is NATURAL JOIN. – lad2025 Jun 12 '18 at 20:04
0

If you don't want to specify each column you could use NOT EXISTS ... NATURAL JOIN.

Warning! This solution is not best from performance point of view. It should work on Oracle/PostgreSQL/SQLite/MariaDB 10.3.2 and above.

Setting up:

CREATE TABLE the_table(
   id           integer not null 
  ,date_          date    
  ,persons       integer 
  ,two_wheelers  integer 
  ,cars          integer 
  ,vans          integer 
  ,buses         integer 
 , autos         integer 
);

INSERT INTO the_table(id, date_, persons, two_wheelers, cars, vans, buses, autos)
VALUES (1, '21/JAN/2018',1,1,1,1,1,1);

INSERT INTO the_table(id, date_, persons, two_wheelers, cars, vans, buses, autos)
VALUES (2, '21/JAN/2018',2,2,2,2,NULL,2);
INSERT INTO the_table(id, date_, persons, two_wheelers, cars, vans, buses, autos)
VALUES (3, '21/JAN/2018',3,3,3,3,NULL,NULL);

SELECT * FROM the_table;

+----+-------------+---------+--------------+------+------+-------+-------+
| id |    date_    | persons | two_wheelers | cars | vans | buses | autos |
+----+-------------+---------+--------------+------+------+-------+-------+
|  1 | 21/JAN/2018 |       1 |            1 |    1 |    1 | 1     | 1     |
|  2 | 21/JAN/2018 |       2 |            2 |    2 |    2 | null  | 2     |
|  3 | 21/JAN/2018 |       3 |            3 |    3 |    3 | null  | null  |
+----+-------------+---------+--------------+------+------+-------+-------+

And query:

DELETE FROM the_table
WHERE NOT EXISTS (SELECT *
                  FROM the_table t1
                  NATURAL JOIN the_table t2
                  WHERE id = the_table.id);

Output:

+----+-------------+---------+--------------+------+------+-------+-------+
| id |    date_    | persons | two_wheelers | cars | vans | buses | autos |
+----+-------------+---------+--------------+------+------+-------+-------+
|  1 | 21/JAN/2018 |       1 |            1 |    1 |    1 |     1 |     1 |
+----+-------------+---------+--------------+------+------+-------+-------+

DBFiddle Demo

|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.