SQL: how to force a relational operator to consider null values
I have 2 tables (TABLE 1 and TABLE 2). I would like to execute the following request:
UPDATE TABLE1 a,TABLE1 b
SET a.desg=CASE WHEN b.attribute_id=74 THEN b.value ELSE a.desc END
WHERE a.entity_id=b.entity_id;
But I have multiple rows in TABLE1 with entity_id as NULL.
They are not considered in the assessmentWHERE a.entity_id=b.entity_id;
I want even NULLs to be considered for this WHERE clause.
How can i do this? Thanks in advance.
+3
Uday
source
to share
2 answers
Try using something like
WHERE IFNULL(a.entity_id, 0) = IFNULL(b.entity_id, 0);
+2
Andrey Gurinov
source
to share
UPDATE TABLE1 a, TABLE1 b
SET a.desg = CASE WHEN b.attribute_id=74 THEN b.value ELSE a.desc END
WHERE a.entity_id=b.entity_id OR (a.entity_id IS NULL AND b.entity_id IS NULL)
+5
levi
source
to share