Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,624 questions
This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
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')
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