Update based on a select

I suggest using INNER JOIN :

UPDATE 
    pedidos a
    INNER JOIN produtos_pedidos b on b.id_pedido = a.id
SET
    a.custo = b.valor

The problem with the original command in the question seems to be the existence of items in the pedidos table for which there is no corresponding item in the produtos_pedidos table. Then, for an order without corresponding product, an error will occur. The INNER JOIN will ensure that only requests with corresponding registration in produtos_pedidos will be obtained for the cost update.

Ps: If the cost field can not accept null values, you can still use a function such as COALESCE to replace null with zero, or include a where clause to filter only when produtos_pedidos / p>     

It may happen that he is not finding any value that hits the a.id.

Try this:

UPDATE 
pedidos a
SET 
custo = (
    SELECT 
        coalesce(b.valor, 0)
    FROM 
        produtos_pedidos b
    WHERE 
        b.id_pedido = a.id
    )

where coalesce(b.valor, 0) returns a default value if it is null, in which case I put 0

You can use a default value if b.value is null or check if b.value is null .

UPDATE 
pedidos a
SET 
custo = (
    SELECT 
        b.valor
    FROM 
        produtos_pedidos b
    WHERE 
        b.id_pedido = a.id
        and b.valor is not null
    )

I would use a query like this.

UPDATE a
SET custo = case when b.valor is null then 0 else  b.valor end
FROM pedidos a
join produtos_pedidos b
on b.id_pedido = a.id