Setting value using a subquery that return's no row on Postgres

Question

I have two tables: tire_life and transaction on my database.

It works fine when the subquery returns a value, but when no row is returned it does not update the tire_life table.

That's the query I'm running.

UPDATE tire_life as life SET covered_distance = initial_durability + transactions.durability 
FROM (SELECT tire_life_id, SUM(tire_covered_distance) as durability  
           FROM transaction WHERE tire_life_id = 24 AND deleted_at IS NULL 
      GROUP BY (tire_life_id)) as transactions 
WHERE life.id = 24

I have tried to use the COALESCE() function with no success at all.

Answer 1

You can use coalesce() for a subquery only if it returns single column. As you do not use tire_life_id outside the subquery, you can skip it:

UPDATE tire_life as life 
SET covered_distance = initial_durability + transactions.durability 
FROM (
    SELECT coalesce(
        (
            SELECT SUM(tire_covered_distance)
            FROM transaction 
            WHERE tire_life_id = 24 AND deleted_at IS NULL 
            GROUP BY (tire_life_id)
        ), 0) as durability
    ) as transactions 
WHERE life.id = 24;

I guess you want to get 0 as durability if the subquery returns no rows.