Update in a column using more than one return

Asked

Viewed 840 times

2

I have to update a certain field of a table by subtracting from it the values returned from a select in another table. The problem I’m facing is that as select returns more than one value and I have to subtract value by value, to be able to compare if the current value is less than the query value, I don’t know how to do this. What I did from the error saying that the result has more than one value and cannot perform. 

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 also in the end would have to update the second table saying which data were used, but n know how to save the ids that were updated.

sql sql-server sql-server-express

  • https://docs.microsoft.com/pt-br/sql/t-sql/language-elements/while-transact-sql read about cursors, below are examples.

2 answers

0

Whereas in the product table there is only one value for each customer and product, and you need to upgrade product 2, subtracting the product value if it is lower.

I think you can simplify this query and solve 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);

  • The problem is that in the product table can have more than one value, it is precisely in this part that I can not, because the search in the product table returns more than one value and n know how to go decreasing each one while still can.

  • What is the limit of "can still" ?

  • Put the table structure please

  • as product value2 is greater than product value 1

  • is just there that I do not know how to go subtracting line by line from select in product, in the value of the current product2.

  • What would be the simplest way to do this subtraction? I’ve tried every way I know and won’t.

  • @Loid reproduces its environment in sqlfiddle.com please

Show 2 more comments

0

Update with FROM this way:

Mysql:

create table produto(clientid integer, valor integer);
create table produto2(clientid integer, valor integer);
insert into produto(clientid , valor) values(1, 100);
insert into produto(clientid , valor) values(2, 200);
insert into produto2(clientid , valor) values(1, 100);
insert into produto2(clientid , valor) values(2, 400);
update produto2 inner join produto on produto.clientid = produto2.clientid set produto2.valor = produto2.valor - produto.valor where produto.valor < produto2.valor;

select * from produto2;

SQL Server:

UPDATE PRODUTO2
SET VALOR = (PRODUTO2.VALOR - P.VALOR)
FROM PRODUTO2
INNER JOIN PRODUTO P (NOLOCK) ON
   P.CLIENTID = PRODUTO2.CLIENTEID
WHERE P.VALOR < PRODUTO2.VALOR

How to know which records will be updated? Just switch to select:

SELECT
PRODUTO2.CLIENTID, PRODUTO2.VALOR, P.VALOR  
FROM PRODUTO2
INNER JOIN PRODUTO P (NOLOCK) ON
   P.CLIENTID = PRODUTO2.CLIENTEID
WHERE P.VALOR < PRODUTO2.VALOR

This is one of the safest ways to perform an UPDATE.

*Atenção*: A mesma tabela que esta no FROM, deverá estar no UPDATE, isto se aplica caso também seja aplicado ALIAS.

Correct examples:

• UPDATE PRODUTO SET VALOR = 1000 FROM PRODUTO WHERE PRODUTO.ID = 3
• UPDATE P SET VALOR = 1000 FROM PRODUTO P WHERE P.ID = 3

INCORRECT EXAMPLE:

• UPDATE PRODUTO FROM PRODUTO P WHERE P.ID = 3

Will SQL allow you to update wrong? Yes. Can this cause inconsistency in the database if you have other relationships? Yes. There is more detailed information about these inconsistencies of the UPDATE clause in the database documentation.

Browser other questions tagged sql sql-server sql-server-express

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