Share via

Update W/O CTE And Using MIN()

Johnathan Simpson 586 Reputation points
2020-10-29T17:34:01.517+00:00

I have the below DDL and my question is how can I update the MIN() itemlineID in @LineItems with the taxAmt from @Taxinfo without using a CTE?

Declare @LineItems Table

(
ordernumber varchar(100)
,itemlinenumber int
,itemlineID varchar(100)
,tax decimal(16,8)
)

Declare @Taxinfo Table
(
ordernumber varchar(100)
,taxamt decimal(16,8)
)

Insert Into @Taxinfo Values
('123A', '23.88'), ('456B', '2.81')

Insert Into @LineItems Values
('123A', 1, '123A-1'), ('123A', 2, '123A-2'), ('123A', 3, '123A-3'), ('123A', 4, '123A-4')
,('456B', 1, '456B-1'), ('456B', 2, '456B-2')
Developer technologies | Transact-SQL
Developer technologies | Transact-SQL

A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.

0 comments No comments

Answer accepted by question author

Viorel 127K Reputation points
2020-10-29T18:02:47.177+00:00

Show the expected results if the next query does not work:

update li1  
set tax = taxamt  
from @LineItems as li1  
inner join @Taxinfo as ti on ti.ordernumber = li1.ordernumber  
left join @LineItems as li2 on li1.ordernumber = li2.ordernumber and li1.itemlinenumber > li2.itemlinenumber  
where li2.itemlinenumber is null  



  

Was this answer helpful?

0 comments No comments

0 additional answers

Sort by: Most helpful

Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.