Clever Geek Handbook

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


source to share


2 answers


Try using something like



WHERE IFNULL(a.entity_id, 0) = IFNULL(b.entity_id, 0);
+2


source 


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


source






More articles:


All Articles