H.C H.C - 1 month ago 5
SQL Question

update query based in values from the subquery

declare @exampleTable table(
id int,
Quantity int
)

declare @exampleTable_Aux table(
id int,
Quantity int
)

insert into @exampleTable values(1,5),(2,8)
insert into @exampleTable_Aux values(1,3),(5,8)

select * from @exampleTable
select di_aux.id from @exampleTable di_aux inner join @exampleTable_Aux dij on di_aux.id=dij.id

declare @Quantity int;

UPDATE di SET Quantity=22
FROM @exampleTable di WHERE di.id in (select di_aux.id from @exampleTable di_aux inner join @exampleTable_Aux dij on di_aux.id=dij.id)


know if what I want is the Quantity to get the value from the subquery for example(invalid only to indicate what I pretend)

UPDATE di SET Quantity=@Quantity+di.Quantity
FROM @exampleTable di WHERE di.id in (select di_aux.id,@Quantity=dij.Quantity from @exampleTable di_aux inner join @exampleTable_Aux dij on di_aux.id=dij.id)


should get the table:
How can i do that if its even possible to update the query base in the values from the subquery?

id Quantity
1 1 8
2 2 8

Answer

You don't need sub-query at all. Just Update the table @exampleTable by joining @exampleTable_Aux

Here is the correct way

UPDATE di_aux
SET    di_aux.Quantity = dij.Quantity + di_aux.Quantity
FROM   @exampleTable di_aux
       INNER JOIN @exampleTable_Aux dij
               ON di_aux.id = dij.id