Update using different record from the same table

Question

I need to make a update update the field record of a table, using another record of the same table and same field. My table is as follows:

select produto,valor_bruto,acrescimo,valor_liquido 
  from tab_itens 
 where data='2021-06-10' 
   and caixa=8 
   and cupom in (145,146)

In coupon 145, the values of the fields "raw value", "accrescimo" and "liquid value" are correct, however, in coupon 146 which is a double sale, the values of the fields "accrescimo" and "liquid value" were wrong.

I tried the following query:

update tab_itens set 
   acrescimo=(select acrescimo 
                from tab_itens 
               where cast(data as date)='2021-06-10' 
                 and caixa=8 
                 and cupom=145)
where cast(data as date)='2021-06-10' 
  and caixa=8 
  and cupom=146;

The result of sql server was:

Message 512, Level 16, Status 1, Line 10 Sub-volume returned more of 1 value. This is not allowed when the sub-consumption follows =, != , <, <= , >, >= or when it is used as an expression.

How do I update the fields "accrescimo" and "valor_bruto" of coupon 146, using the same fields only of coupon 145?

Table structure

Answer 1

The problem is that in your command of update subconsulta is returning more than 1 record as reported in the error message.

To correct, you will need to relate the sub-allowance in such a way that you will return only 1 record.
For this purpose to return only one value per record, add the fields item and cod_produto in the subconsultation:

update tab_itens set 
   acrescimo=(select acrescimo 
                from tab_itens cp145
               where cast(data as date)='2021-06-10' 
                 and caixa=8 
                 and cupom=145
                 and cp145.item = tab_itens.item 
                 and cp145.cod_produto = tab_itens.cod_produto)
where cast(data as date)='2021-06-10' 
  and caixa=8 
  and cupom=146; 

Was assigned alias cp145 to refer to tab_itens in the subconsultation:

from tab_itens cp145

on coupon 146 which is a double sale, the values of the fields "acrescimo" and "valor_liquido" were wrong.

Whereas the coupons are a double sale, the fields Item and Cod_product should be equal in both records, and should be used to perform the search constraint on the subconsulta, which will result in only 1 value for each item, thus allowing the update. So I added the following supplement to your update:

and cp145.item = tab_itens.item 
and cp145.cod_produto = tab_itens.cod_produto

Follow the example to update the "liquid value".

I performed a test that is available on http://sqlfiddle.com/#! 18/da66d8/1.