SQL update, set, case: What am I doing wrong?

Question

Table:Person

+------+-------+
| name |  age  |
+------+-------+
| abc  |  NULL |
+------+-------+

I want to update age in two conditions

1. If age in the table is NULL, set it to the var $year
2. If age in the table is NOT NULL, increase it by $year

I have the following code. But it does not update my age in table. What am I doing wrong?

$sql = "UPDATE `Person`
        SET age =
            CASE
                WHEN age == null THEN $year
                ELSE age + $year
            END
        WHERE name='abc'";
mysqli_query($conn, $sql);

Answer 1

You can't compare a value with null directly by doing age = null, as null has no type and is sort of "undefined". You need to check if the value is null, by doing CASE WHEN age IS NULL.

You should also be using a prepared statement here, since you're dealing with variables directly in the query.

$name = 'abc';
$sql = "UPDATE `Person`
        SET age =
            CASE
                WHEN age IS NULL THEN ?
                ELSE age + ?
            END
        WHERE name = ?";
$stmt = $conn->prepare($sql);
$stmt->bind_param("iis", $year, $year, $name);
$stmt->execute();
$stmt->close();

You can reduce the number of parameters a bit by first getting the current age, or zero if it was null, then adding the $year.

$name = 'abc';
$sql = "UPDATE `Person`
        SET age = (CASE WHEN age IS NULL THEN 0 ELSE age END) + ?
        WHERE name = ?";
$stmt = $conn->prepare($sql);
$stmt->bind_param("is", $year, $name);
$stmt->execute();
$stmt->close();