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')
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,624 questions
0 comments No comments
{count} votes

Accepted answer
  1. Viorel 116.6K 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  
    
    
    
      
    
    0 comments No comments

0 additional answers

Sort by: Most helpful

Your answer

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