Update on a column using more than one return

Question

Update on a column using more than one return

Navigation

#1 by (0 votes)

2

I have to update a given field of a table by subtracting from it the values returned from a select in another table. The problem I am facing is that as select returns more than one value and I have to go subtracting value by value, so I can compare if the current value is less than the value of the query, I do not know how to do this. What I did of the error saying that the result has more than one value and can not execute.

update Produto2 set valor = case when (select p.valor from Produto p where p.clienteId = clienteId) < valor then 
valor - (select p.valor from Produto p where p.id = produtoId) else
valor - 0 end;

And furthermore in the end I would have to update the second table by telling which data was used, but I do not know how to store the ids that were updated.

sql sql-server sql-server-express

asked by anonymous 14.11.2017 / 00:05

1 answer

What are the main design patterns in NODEJS for Web development? [closed] Ionic 3 build error

score 0 · Answer 1

Considering that in the product table there is only one value for each customer and product, and that you need to update product2, subtracting the product value if it is less.

I think you can simplify this query and resolve it as follows:

update produto2 set 
    valor = valor - coalesce((select 
                                  p.valor 
                              from produto p 
                              where p.clienteId = clienteId 
                              and p.id = produtoId 
                              and p.valor < valor),0);