Update SQLite row only if a column is empty

user_15990864 0
Viewed 47

I have the following:

$query = $db->prepare("UPDATE name SET one=?,two=?,three=? WHERE id = ?");
$query->bindParam(1, $one);
$query->bindParam(2, $two);
$query->bindParam(3, $three);
$query->bindParam(4, $id);

one and three should always be updated.

However, how can I update two only if it is null/empty?

I can do this in a separate command, though how can I achieve this in one prepared statement?

I am using SQLite3

case sql-update sqlite sql php
2 Answers

Use a CASE expression for column two:

UPDATE name 
SET one=?,
    two=CASE WHEN two IS NULL THEN ? ELSE two END,
    three=? 
WHERE id = ?

Or with COALESCE():

UPDATE name 
SET one=?,
    two=COALESCE(two, ?),
    three=? 
WHERE id = ?
user_104988280

Use case

UPDATE name 
SET one=?,two=case when two is null or two = '' then ? else two end, three=? 
WHERE id = ?
user_62199790
Related