CODEWITHSUNDEEP
mysqlsqlsql-delete
PeterPan2020
PeterPan2020

Reputation: 172

Delete rows with same columns data

I am having a temp table with the following data:

tbl_t
id   name_id     date      t1    t2    s1    s2
 1      25     10/05/20   same  same  NULL  NULL
 2      23     11/05/21   same  same  home  NULL
 3      25     12/05/20   same  NULL  NULL  NULL
 4      25     13/06/20   NULL  NULL  NULL  NULL

Desire output:

tbl_t
id   name_id     date      t1    t2    s1    s2
 2      23     11/05/21   same  same  home  NULL
 3      25     12/05/20   same  NULL  NULL  NULL

I want to delete all rows where t1=t2 and s1=s1

I tried the following sql but i noticed that it is not working.

DELETE FROM tbl_t WHERE t1=t2 AND s1=s2

Upvotes: 1

Views: 68

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269463

The problem are the NULL values. Use the NULL-safe comparison operator:

DELETE FROM tbl_t
    WHERE t1 <=> t2 AND s1 <=> s2;

Almost any comparison with NULL results in NULL -- including NULL = NULL. And NULL values are treated as false in a WHERE clause (or equivalently WHERE clauses only keep rows where the condition evaluates unequivocally to true).

Upvotes: 1

Related Questions