Update based on a select

Asked

Viewed 1,242 times

4

I have to update the field custo table pedidos based on the field valor table produtos_pedidos.

Whereas the a.id table pedidos must be equal to the field b.id_pedido table produtos_pedidos.

I’m trying to do it like this:

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

I’m making that mistake:

Coluna 'custo' não pode ser vazia
mysql
  • 1

    This is the whole update? can be the select returned some record with null in the value column.

  • That’s probably what’s going on.

3 answers

4


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 in the original command in the question seems to be the existence of items in the table pedidos for which there is no corresponding item in the table produtos_pedidos. Then, for an order with no co-product, error will occur. The INNER JOIN will ensure that only orders with co-registration in produtos_pedidos will be obtained for cost update.

P.s.: If the cost field cannot accept null values, you can still use a function such as COALESCE to replace null by zero, or include a Where clause to filter only when the records of produtos_pedidos have value.

  • if the cost field does not accept null values and in the value field accept the error will continue to happen.

  • @Marconciliosouza well remembered, thanks.

3

It may happen that he’s not finding any value that matches the a.id.

Try it like this:

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

where the coalesce(b.valor, 0) returns a default value if null, in which case 0

  • 2

    You made the same mistake your way.

  • try to put the coalesce in the cost = coalesce (select)

  • 2

    not working, look at the example I made.http://sqlfiddle.com/#! 9/0bdaef

2

You can use a default value if b.value is null or check whether the 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

  • It did not work in any way friend. In the first it continues the error saying that the cost cannot be null, and in the second of a syntax error

Browser other questions tagged mysql

You are not signed in. Login or sign up in order to post.