'Update query inserts null in rows which are not mentioned for the same id

This is my update query

UPDATE customer_entity_varchar
    SET value = (case when attribute_id = 5 then '$fname'
                         when attribute_id = 6 then '$mname'
                         when attribute_id = 7 then '$lname'
                    end)
where entity_id='$entityId'

This query works abnormally it does inserts value for attribute_id with values 5,6,7 but I have more attribute_id each with a different value in the "value" column. When I fire the above query it makes all the values corresponding to all other attribute_id = NULL. I don't want that! I want all other attribute_id values to remain untouched. How do I achieve this?

mysqlsqlsql-update


Solution 1:[1]

Use an else statement in your case statement:

UPDATE customer_entity_varchar
SET value = (case when attribute_id = 5 then '$fname'
                  when attribute_id = 6 then '$mname'
                  when attribute_id = 7 then '$lname'
                  else value
             end)
where entity_id='$entityId'

Or you can use where criteria.

UPDATE customer_entity_varchar
SET value = (case when attribute_id = 5 then '$fname'
                  when attribute_id = 6 then '$mname'
                  when attribute_id = 7 then '$lname'
             end)
where entity_id='$entityId' and attribute_id in (5,6,7)

Solution 2:[2]

If an else is not provided in a CASE expression, NULL is used.

If there was no matching result value, the result after ELSE is returned, or NULL if there is no ELSE part.

Solution 3:[3]

In your where statement add:

AND attribute_id IN (5,6,7)

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1
Solution 2 Uueerdo
Solution 3 Arleigh Reyna

Related Questions